Skip to content

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

Critical

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
create table locations (
   location_id        number(10)        not null
  ,location_name      varchar2(60 char) not null
  ,city               varchar2(30 char) not null
  ,constraint locations_pk primary key (location_id)
  )
/

create sequence location_seq start with 1 cache 20
/

create or replace trigger location_br_i
before insert on locations
for each row
begin
   :new.location_id := location_seq.nextval;
end;
/

Example (good)

1
2
3
4
5
6
create table locations (
   location_id        number(10)  generated always as identity
  ,location_name      varchar2(60 char) not null
  ,city               varchar2(30 char) not null
  ,constraint locations_pk primary key (location_id))
/

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.