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

Minor

Maintainability, Reliability

Restriction

ORACLE 12c or higher

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

create sequence location_seq start with 1 cache 20
/

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

Example (good)

1
2
3
4
5
6
create table location (
   location_id        number(10)  generated by default on null as identity 
  ,location_name      varchar2(60 char) not null
  ,city               varchar2(30 char) not null
  ,constraint location_pk primary key (location_id))
/