Identity Column
Last updated:
Warning: Review and test in a non-production environment before running.
/*
SYNTAX :
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]
*/
create table G_ALWAYS(
id NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1 INCREMENT BY 1,
description VARCHAR2(100) NOT NULL
);
insert into G_ALWAYS(description) values('First row');
insert into G_ALWAYS(description) values('Second row');
insert into G_ALWAYS(id,description) values(5,'Second row');
select * from G_ALWAYS;
/*
If we try to pass a value to id will will get an error :
SQL Error: ORA-32795: cannot insert into a generated always identity column
*/
create table G_DEFAULT(
id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1,
description VARCHAR2(100) NOT NULL
);
insert into G_DEFAULT(description) values('First row');
insert into G_DEFAULT(id,description) values(2,'Second row');
insert into G_DEFAULT(id,description) values(NULL,'Second row');
select * from G_DEFAULT;
/*
If we pass NULL to id column we will get an error :
SQL Error: ORA-01400: cannot insert NULL into ("OT"."G_ALWAYS"."ID")
*/
create table G_NULL(
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1,
description VARCHAR2(100) NOT NULL
);
insert into G_NULL(description) values('First row');
insert into G_NULL(description) values('Second row');
insert into G_NULL(id,description) values(NULL,'Fourth row');
select * from G_NULL;
/*
If we pass NULL to identity column it will generate the next value.
*/