PRIMARY KEY / UNIQUE

Description

All of the optional clauses of the PRIMARY KEY / UNIQUE constraint are removed in Snowflake.

Syntax in SQL Server

{ PRIMARY KEY | UNIQUE }
    [ CLUSTERED | NONCLUSTERED ]
    [ WITH FILLFACTOR = fillfactor ]
    [ WITH ( index_option [, ...n ] ) ]
    [ ON { partition_scheme_name (partition_column_name)
        | filegroup | "default" } ]

Sample Source Patterns

SQL Server

ALTER TABLE table_name
ADD column_name INTEGER
CONSTRAINT constraint_name UNIQUE;

ALTER TABLE table_name
ADD column_name INTEGER
CONSTRAINT constraint_name PRIMARY KEY
NONCLUSTERED;

ALTER TABLE table_name
ADD column_name INTEGER
CONSTRAINT constraint_name UNIQUE
WITH FILLFACTOR = 80;

ALTER TABLE table_name
ADD column_name INTEGER
CONSTRAINT constraint_name PRIMARY KEY
WITH (PAD_INDEX = off);

ALTER TABLE table_name
ADD column_name INTEGER
CONSTRAINT constraint_name UNIQUE
ON partition_scheme_name (partition_column_name);

Snowflake

ALTER TABLE PUBLIC.table_name
ADD column_name INTEGER CONSTRAINT constraint_name UNIQUE;

ALTER TABLE PUBLIC.table_name
ADD column_name INTEGER CONSTRAINT constraint_name PRIMARY KEY
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--NONCLUSTERED
            ;

ALTER TABLE PUBLIC.table_name
ADD column_name INTEGER CONSTRAINT constraint_name UNIQUE
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH FILLFACTOR - THIS IS NON-RELEVANT **
--WITH FILLFACTOR = 80
                    ;

ALTER TABLE PUBLIC.table_name
ADD column_name INTEGER CONSTRAINT constraint_name PRIMARY KEY
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH INDEX - THIS IS NON-RELEVANT **
--WITH (PAD_INDEX = off)
                      ;

ALTER TABLE PUBLIC.table_name
ADD column_name INTEGER CONSTRAINT constraint_name UNIQUE
-- ** MSC-WARNING - MSCEWI1042 - Commented ON PARTITION - THIS IS NON-RELEVANT **
-- ON partition_scheme_name (partition_column_name);

Related EWIs

PreviousFOREIGN KEYNextCHECK

Last updated