G-3150: Try to use identity columns for surrogate keys.
Minor
Maintainability, Reliability
Restriction
Oracle Database 12c
Reason
An identity column is a surrogate key by design – there is no reason why we should not take advantage of this natural implementation when the keys are generated on database level. Using identity column (and therefore assigning sequences as default values on columns) has a huge performance advantage over a trigger solution.
Example (bad)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
|
Example (good)
1 2 3 4 5 6 |
|
generated always as identity
ensures that the location_id
is populated by a sequence. It is not possible to override the behavior in the application.
However, if you use a framework that produces an insert
statement including the surrogate key column, and you cannot change this behavior, then you have to use the generated by default on null as identity
option. This has the downside that the application may pass a value, which might lead to an immediate or delayed ORA-00001: unique constraint violated
error.