identity column, GUID, and default sequence value as surrogate primary key

What is a surrogate key

A surrogate key is a type of primary key used in most database tables. It provides a simple, system-generated, business-agnostic column.

Here are three options that oracle 19 gives us when we want to create a surrogate primary key ( surrogate PK):

1. Identity column
2. GUID
3. Sequance ()

Identity column

The identity column is very useful for the surrogate primary key column. When we insert a new row into the identity column, Oracle auto-generates and insert a sequential value into the column. If tou want to define an identity column, you use the identity clause as shown below:

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] 

For example:

CREATE TABLE identity_demo (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    description VARCHAR2(100) NOT NULL
);

INSERT INTO identity_demo(description) VALUES('This is a test');

But if we try to insert value into the id colomn:

INSERT INTO identity_demo
      (id, description)
VALUES(2, 'Identity column example with GENERATED ALWAYS will fail!');    
SQL Error: ORA-32795: cannot insert into a generated always identity column

GENERATED options

• GENERATED ALWAYS - Oracle generates a value for the identity column. Attempt to insert a value into the identity column will cause an error.
• GENERATED BY DEFAULT - Oracle generates a value for the identity column if you provide no value. If you provide a value, Oracle will insert that value into the identity column. For this option, Oracle will issue an error if you insert a NULL value into the identity column.
• GENERATED BY DEFAULT ON NULL - Oracle generates a value for the identity column if you provide a NULL value or no value at all.

GENERATED options

• GENERATED ALWAYS - Oracle generates a value for the identity column. Attempt to insert a value into the identity column will cause an error.
• GENERATED BY DEFAULT: - Oracle generates a value for the identity column if you provide no value. If you provide a value, Oracle will insert that value into the identity column. For this option, Oracle will issue an error if you insert a NULL value into the identity column.
• GENERATED BY DEFAULT ON NULL: Oracle generates a value for the identity column if you provide a NULL value or no value at all.

main options of usage

• START WITH initial_value - The default initial value is 1.
• INCREMENT BY internval_value defines the interval between generated values. By default, the interval value is 1.
• CACHE - defines a number of values that Oracle should generate beforehand to improve the performance. We should use this option when We expect high number of inserts.

Generating a Default Value from a SEQUENCE

Referencing a sequence as a column default value in a create table statement. New with database 12c. So both the i

CREATE TABLE db_12c_style_identity 
(  
id               INTEGER  DEFAULT ON NULL db_id_test_seq.nextval PRIMARY KEY, 
another_column   VARCHAR2(30) 
)

Generating a Default Value from a GUID

A GUID (Globally Unique Identifier) is a 128-bit text string that represents an identification (ID). Creating a guid column can by very usful as alternative to PK. for example:

CREATE TABLE db_12c_style_guid
(  
guid_column      RAW(16)         DEFAULT sys_guid() 
another_column   VARCHAR2(30) 
)

BTW, one can convert GUID eo an number:

to_number(sys_guid(),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ) from dual;