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 Person
ADD
CONSTRAINT PK_EmailAddress_BusinessEntityID_EmailAddressID PRIMARY KEY (BusinessEntityID, EmailAddressID);
-- FOREING KEY TO ANOTHER TABLE
ALTER TABLE Person.EmailAddress
!!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
WITH CHECK
ADD
CONSTRAINT FK_EmailAddress_Person_BusinessEntityID FOREIGN KEY(BusinessEntityID) REFERENCES Person.Person (BusinessEntityID) ON DELETE CASCADE ;
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 Table1 (
COL_VARCHAR VARCHAR DEFAULT ('NOT DEFINED'),
COL_INT INT DEFAULT ((0)),
COL_DATE DATE DEFAULT (CURRENT_TIMESTAMP() :: TIMESTAMP)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
----** SSC-FDM-TS0020 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION **
--ALTER TABLE Table1
--ADD
-- CONSTRAINT DF_Table1_COL_INT DEFAULT ((0)) FOR COL_INT
;
ALTER TABLE Table1
ADD COL_NEWCOLUMN VARCHAR;
----** SSC-FDM-TS0020 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION **
--ALTER TABLE Table1
--ADD
--CONSTRAINT DF_Table1_COL_VARCHAR DEFAULT ('NOT DEFINED') FOR COL_VARCHAR
;
----** SSC-FDM-TS0020 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION **
--ALTER TABLE Table1
--ADD
-- CONSTRAINT DF_Table1_COL_DATE DEFAULT (CURRENT_TIMESTAMP() :: TIMESTAMP) FOR COL_DATE
;
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
SSC-EWI-0035: Check statement not supported.
SSC-EWI-0040: Statement Not Supported.
SSC-FDM-TS0020: Default constraint was commented out and may have been added to a table definition.
Last updated