How to Add an Identity Column to Existing Snowflake Table?

Because of its unique architecture, Snowflake does not support column constraints such as primary, foreign or unique key column. The only constraint supported is NOT NULL. But, many traditional database architects would like to keep primary keys and populate with unique key values. You can define the identity columns or create sequences and add sequence value as a default. But, you can’t add an identity column to the existing table with data. In This article, we will check how to add an identity column to the existing Snowflake table.

Add an Identity Column to Existing Snowflake Table

Having an identity column or primary key on Snowflake tables allows you to write optimal queries that use a primary key column in its join. Moreover, constraints provide valuable metadata. The primary keys and foreign keys enable members of your project team to orient themselves to the schema design and familiarize themselves with how the tables relate with one another.

You can’t add an identity column to the existing table. In order to add identity column, table must be empty. Otherwise, you will end up with an error “SQL compilation error: Cannot add column ‘column1’ with non-constant default to non-empty table ‘table1′”.

For example,

ALTER TABLE "EMPLOYEE_TEMP" ADD COLUMN "id2" int IDENTITY(1,1);
002201 (22023): SQL compilation error: Cannot add column 'id2' with non-constant default to non-empty table 'EMPLOYEE_TEMP'.

The following steps allows you to add an identity column to the existing non-empty table in Snowflake.

-- Create temporary table
CREATE OR REPLACE TABLE EMPLOYEE_TEMP LIKE EMPLOYEE;

-- Create Sequence to generate unique number
CREATE OR REPLACE SEQUENCE uniqueIDseq1;

-- Alter table to add Primary key column and assign default value
ALTER TABLE EMPLOYEE_TEMP
ADD COLUMN id int DEFAULT uniqueIDseq1.nextval;

-- Insert the records into temp table. Note, last column is value from sequence
INSERT INTO EMPLOYEE_TEMP
SELECT *, uniqueIDseq1.nextval 
FROM EMPLOYEE;

-- Drop original Table
DROP TABLE EMPLOYEE;

-- Rename temp to original table
ALTER TABLE EMPLOYEE_TEMP RENAME TO EMPLOYEE;

Please note that, this method works well with a table with a small amount of data. But, Snowflake will take a considerable amount of time and resource if you plan to add an identity column to a comparatively big table.

Bottom Line

You should use this method only when necessary. And make sure table size is small. It is not recommended for large table. The architecture of Snowflake is unique and stores data in many micro-partition. You may incur lost of computing cost to reorganize all those micro-partitions.

Hope this helps 🙂