sql / expert
Snippet
Identity Control with OVERRIDING SYSTEM VALUE
When a column is defined as GENERATED ALWAYS AS IDENTITY, the database forbids manual value insertion. The OVERRIDING SYSTEM VALUE clause bypasses this restriction, allowing for manual key specification during data migrations while maintaining the generator sequence.
snippet.sql
1
2
3
INSERT INTO system_users (id, username)OVERRIDING SYSTEM VALUEVALUES (1001, 'legacy_admin');
Breakdown
1
INSERT INTO system_users (id, username)
Initiates the insertion into a table containing an identity column.
2
OVERRIDING SYSTEM VALUE
Instructs the SQL engine to prioritize the provided value over the automatic sequence generator for this row.
3
VALUES (1001, 'legacy_admin')
The specific manual value provided for the identity-protected ID column.