Identity column Constraint

Table Columns having Identity constraints can be termed as Identity column. They can be used as primary key constraint for the table.

Following is syntax to declare Identity column constraint.

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

1. GENERATED : It is mandatory keyword.
2. ALWAYS : It indicates that value for this column would be always generated by database. If we explicity try to set value for this column it would result in error.
3. BY DEFAULT : It indicates that value for this column are generated automatically only when no value is provided while inserting record into table.
4. BY DEFAULT ON NULL : It indicates that value for this column are generated only when no value provider or null value provided while inserting record into table.
5. AS IDENTITY : it is mandatory keywords.
6. identity_options : We can add multiple options to control generation of value.
   • START WITH initial_value – value generation will start from initial_value. If not provided, value generation starts from 1.
   • INCREMENT BY interval_value – Defines interval to generate value. Default interval is 1.
   • CACHE – This is used to improve performance of insertion queries. It indicates number of values to be generated in advanced.