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

  1. MSCEWI1088: Expressions like function calls, variables, or named constants are not allowed on default option in Snowflake

  2. MSCEWI1042: Non-relevant syntax

  3. MSCEWI4058: One or more of the table element parts are not supported in snowflake.

PreviousCHECKNextFOREIGN KEY

Last updated