G-3150: Minor Update

[RichardSoule]

G-3150: Try to use identity columns for surrogate keys.

Currently has this as the good example:

location_id number(10) generated always as identity

However, the most flexible identity configuration is actually this:

location_id number(10) generated by default on null as identity

[PhilippSalvisberg]

There are the following three options for identity columns:

1. Default:
   generated by default as identity

2. Default on null:
   generated by default on null as identity

3. Always:
   generated always as identity

And the following three use cases are relevant:

1. Skip:
   insert into locations (location_name, city) values ('...', '...')

2. Pass null:
   insert into locations (location_id, location_name, city) values(null, '...', '...')

3. Pass value:
   insert into locations (location_id, location_name, city) values(1, '...', '...')

Here's an overview of the results per use case and option:

Use case	Default	Default on null	Always
Skip	success	success	success
Pass null	ORA-01400	success	ORA-32795
Pass value	success or ORA-00001	success or ORA-00001	ORA-32795

The “Always” option is the the only option that enforces the creation of the surrogate key by a sequence. 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 “Default on null” option. However, this option also allows to pass an own value for the surrogate key column. This is bypassing the sequence generator and is most probably unwanted.

I think we should add a shorter version of this reasoning to the rule instead of changing the good example.