TABLE CONSTRAINT

Some parts in the output code are omitted for clarity reasons.

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

Query

IN -> SqlServer_01.sql

-- 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

Query

OUT -> SqlServer_01.sql

-- PRIMARY KEY
ALTER TABLE Person
ADD
    CONSTRAINT PK_EmailAddress_BusinessEntityID_EmailAddressID PRIMARY KEY
                                                                           !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!! CLUSTERED (BusinessEntityID, EmailAddressID);

-- 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 ;

DEFAULT within constraints

SQL Server

Query

IN -> SqlServer_02.sql

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

Query

OUT -> SqlServer_02.sql

CREATE OR REPLACE TABLE Table1 (
   COL_VARCHAR VARCHAR,
   COL_INT INT DEFAULT ((0)),
   COL_DATE DATE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

ALTER TABLE Table1
ADD
    CONSTRAINT DF_Table1_COL_INT
                                 !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                                 DEFAULT ((0)) FOR [COL_INT];

                                 ALTER TABLE Table1
                                 ADD COL_NEWCOLUMN VARCHAR,
                                     CONSTRAINT DF_Table1_COL_VARCHAR
                                                                      !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                                                                      DEFAULT ('NOT DEFINED') FOR [COL_VARCHAR];

                                 ALTER TABLE Table1
                                 ADD
    CONSTRAINT DF_Table1_COL_DATE
                                  !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                                  DEFAULT (getdate()) 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

1. SSC-EWI-0040: Statement Not Supported.

2. SSC-EWI-TS0055: Default constraint was commented out and may have been added to a table definition.