Snowflake - AutoIncrement Column

Autoincrement, as the name suggest, is used to increment the value of a column automatically when a new row is inserted into the table. Usually, it is used for the primary key column of the table. The column should be of numberic data type.

In Snowflake, the keyword identity is synonymous to autoincrement.

Syntax :

column_name numeric_datatype autoincrement start_num end_num ORDER|NOORDER

Example

employee_id integer autoincrement start 1 step 1 ORDER

Pitfall :

While recreating the table, we need to pay special attention to auto-increment column. Usually, We take a backup of the table and re-insert rows after recreation. After recreating the table, the autoincrement starts from the intial value again. It would lead to duplicate values in that column. Even having that column as primary key will not help, as primary key is not enforced in snowflake tables (except hybrid table), the inserts will not fail. While re-inserting the old rows without including the autoincrement column might help, we need to consider the possibility of new values getting assigned to different rows and the change of values might after FK relationship with other tables (if any). One possible solution to both scenario is chanege the start value to max+1 of the already existing values while recreating the table.