As we move our platform onto Snowflake I’ve been testing various aspects out on Snowflake. Usually when creating new tables we can set the column to have a primary key at creation for example if you were working on SQL Server you can use the following:
CREATE TABLE TABLE_A ( ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ... );
The code above would create TABLE_A with an auto incrementing primary key starting at 1 and going up by 1 for each row that is added.
Snowflake requires the user to create a sequence first before the table is created. Below is an example of creating a sequence with the name SEQ_TABLE_A. The ID column will start with 1 and increment by 1 each time a new row is added.
SEQUENCE SEQ_TABLE_A START WITH
1 INCREMENT BY 1;
To apply this sequence to the newly created table we apply this sequence to the column where we’d want the auto incrementing primary key.
CREATE OR REPLACE TABLE TABLE_A( ID number default SEQ_TABLE_A.nextval, ... );
A sequence is not needed for each table but the same sequence can be applied to multiple tables. To check what the next value in the sequence will be you can use the following:
SELECT SEQ_TABLE_A.NEXTVAL A;
I’m including a working example with this post. The same sequence is applied to 3 different tables all having different identity values in column ID.
CREATE SEQUENCE SEQ_TABLE_A START WITH 1 INCREMENT BY 1; CREATE OR REPLACE TABLE TABLE_A( ID NUMBER DEFAULT SEQ_TABLE_A.nextval, NAME VARCHAR(50) ); CREATE OR REPLACE TABLE TABLE_B( ID NUMBER DEFAULT SEQ_TABLE_A.nextval, ADDRESS VARCHAR(50) ); CREATE OR REPLACE TABLE TABLE_C( ID NUMBER DEFAULT SEQ_TABLE_A.nextval, CONTACT VARCHAR(50) ); INSERT INTO TABLE_A(NAME) VALUES ('BOB'); INSERT INTO TABLE_B(ADDRESS) VALUES ('LEICESTER'); INSERT INTO TABLE_C(CONTACT) VALUES ('0116'); SELECT * FROM TABLE_A; SELECT * FROM TABLE_B; SELECT * FROM TABLE_C;