TABLE CONSTRAINT
Description
Specifies the properties of a PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK constraint that is part of a new column definition added to a table by using ALTER TABLE. (https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-column-constraint-transact-sql)
Translation for column constraints is relatively straightforward. There are several parts of the syntax that are not required or not supported in Snowflake.
These parts include:
CLUSTERED | NONCLUSTERED
WITH FILLFACTOR = fillfactor
WITH ( index_option [, ...n ] )
ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" }
NOT FOR REPLICATION
CHECK [ NOT FOR REPLICATION ]
Syntax in SQL Server
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
(column [ ASC | DESC ] [ ,...n ] )
[ WITH FILLFACTOR = fillfactor
[ WITH ( <index_option>[ , ...n ] ) ]
[ ON { partition_scheme_name ( partition_column_name ... )
| filegroup | "default" } ]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CONNECTION
( { node_table TO node_table }
[ , {node_table TO node_table }]
[ , ...n ]
)
[ ON DELETE { NO ACTION | CASCADE } ]
| DEFAULT constant_expression FOR column [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
Syntax in Snowflake
inlineUniquePK ::=
[ CONSTRAINT <constraint_name> ]
{ UNIQUE | PRIMARY KEY }
[ [ NOT ] ENFORCED ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY { DEFERRED | IMMEDIATE } ]
[ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ]
[ RELY | NORELY ]
[ CONSTRAINT <constraint_name> ]
{ UNIQUE | PRIMARY KEY }
[ [ NOT ] ENFORCED ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY { DEFERRED | IMMEDIATE } ]
[ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ]
[ RELY | NORELY ]
Sample Source Patterns
Multiple ALTER TABLE instances
SQL Server
-- PRIMARY KEY
ALTER TABLE
[Person]
ADD
CONSTRAINT [PK_EmailAddress_BusinessEntityID_EmailAddressID] PRIMARY KEY CLUSTERED (
[BusinessEntityID] ASC,
[EmailAddressID] ASC
) ON [PRIMARY]
GO
-- FOREING KEY TO ANOTHER TABLE
ALTER TABLE
[Person].[EmailAddress] WITH CHECK
ADD
CONSTRAINT [FK_EmailAddress_Person_BusinessEntityID] FOREIGN KEY([BusinessEntityID]) REFERENCES [Person].[Person] ([BusinessEntityID]) ON DELETE CASCADE
GO
Snowflake
-- PRIMARY KEY
ALTER TABLE PUBLIC.Person
ADD
CONSTRAINT PK_EmailAddress_BusinessEntityID_EmailAddressID PRIMARY KEY (BusinessEntityID /*** MSC-INFORMATION - MSCINF0019 - Column sorting is not supported in Snowflake ***/, EmailAddressID /*** MSC-INFORMATION - MSCINF0019 - Column sorting is not supported in Snowflake ***/)
-- ** MSC-WARNING - MSCEWI1042 - Commented ON PRIMARY - THIS IS NON-RELEVANT **
-- ON PRIMARY
;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO
-- FOREING KEY TO ANOTHER TABLE
ALTER TABLE Person.EmailAddress WITH CHECK
ADD
CONSTRAINT FK_EmailAddress_Person_BusinessEntityID FOREIGN KEY(BusinessEntityID) REFERENCES Person.Person (BusinessEntityID) ON DELETE CASCADE ;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO
DEFAULT within constraints
SQL Server
CREATE TABLE Table1
(
COL_VARCHAR VARCHAR,
COL_INT INT,
COL_DATE DATE
);
ALTER TABLE
Table1
ADD
CONSTRAINT [DF_Table1_COL_INT] DEFAULT ((0)) FOR [COL_INT]
GO
ALTER TABLE
Table1
ADD
COL_NEWCOLUMN VARCHAR,
CONSTRAINT [DF_Table1_COL_VARCHAR] DEFAULT ('NOT DEFINED') FOR [COL_VARCHAR]
GO
ALTER TABLE
Table1
ADD
CONSTRAINT [DF_Table1_COL_DATE] DEFAULT (getdate()) FOR [COL_DATE]
GO
Snowflake
CREATE OR REPLACE TABLE PUBLIC.Table1 (
COL_VARCHAR VARCHAR,
COL_INT INT DEFAULT ((0)),
COL_DATE DATE
);
-- ** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: DEFAULT TABLE CONSTRAINT **
--ALTER TABLE PUBLIC.Table1
--ADD
-- CONSTRAINT DF_Table1_COL_INT DEFAULT ((0)) FOR [COL_INT]
;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO
ALTER TABLE PUBLIC.Table1 ADD COL_NEWCOLUMN VARCHAR
--** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: DEFAULT TABLE CONSTRAINT **
--CONSTRAINT DF_Table1_COL_VARCHAR DEFAULT ('NOT DEFINED') FOR [COL_VARCHAR],
;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO
-- ** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: DEFAULT TABLE CONSTRAINT **
--ALTER TABLE PUBLIC.Table1
--ADD
-- CONSTRAINT DF_Table1_COL_DATE DEFAULT (getdate()) FOR [COL_DATE]
;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO
```
Known Issues
1. DEFAULT is only supported within CREATE TABLE
and ALTER TABLE ... ADD COLUMN
SQL Server supports defining a DEFAULT
property within a constraint, while Snowflake only allows that when adding the column via CREATE TABLE
or ALTER TABLE ... ADD COLUMN
. DEFAULT
properties within the ADD CONSTRAINT
syntax are not supported and will be translated to ALTER TABLE ALTER COLUMN.
Related EWIs
MSCEWI1088: Expressions like function calls, variables, or named constants are not allowed on default option in Snowflake
MSCEWI1042: Non-relevant syntax
MSCEWI4058: One or more of the table element parts are not supported in snowflake.
Last updated
Was this helpful?