CONSTRAINT

Description

Constraints are used to specify rules for the data in a table.

Click here to navigate to the IBM DB2 docs page for this syntax.

Some CONSTRAINT options are migrated as is to Snowflake but some of them are removed because of platform differences. Check out the code example to learn more.

Grammar Syntax

Sample Source Patterns

IBM DB2

Code

CREATE TABLE T1
(
    COL1 INTEGER CONSTRAINT CN1 PRIMARY KEY,
    COL2 INTEGER CONSTRAINT CN2 PRIMARY KEY ENFORCED,
    COL3 INTEGER CONSTRAINT CN3 PRIMARY KEY NOT ENFORCED,
    COL4 INTEGER CONSTRAINT CN4 PRIMARY KEY NOT ENFORCED TRUSTED,
    COL5 INTEGER CONSTRAINT CN5 PRIMARY KEY NOT ENFORCED NOT TRUSTED,
    COL6 INTEGER CONSTRAINT CN6 PRIMARY KEY ENABLE QUERY OPTIMIZATION,
    COL7 INTEGER CONSTRAINT CN7 PRIMARY KEY DISABLE QUERY OPTIMIZATION
    
    COL8 INTEGER CONSTRAINT CN8 UNIQUE,
    COL9 INTEGER CONSTRAINT CN9 CHECK(COL9 < 3),
    
    COL10 INTEGER CONSTRAINT CN10 REFERENCES T2 (COL1, COL3)
    COL11 INTEGER CONSTRAINT CN11 REFERENCES T2 (COL1, COL3) ON DELETE CASCADE,
    COL12 INTEGER CONSTRAINT CN12 REFERENCES T2 (COL1, COL3) ON DELETE RESTRICT,
    COL13 INTEGER CONSTRAINT CN13 REFERENCES T2 (COL1, COL3) ON DELETE SET NULL,
    COL14 INTEGER CONSTRAINT CN14 REFERENCES T2 (COL1, COL3) ON DELETE NO ACTION,
    COL15 INTEGER CONSTRAINT CN15 REFERENCES T2 (COL1, COL3) ON UPDATE NO ACTION,
    COL16 INTEGER CONSTRAINT CN16 REFERENCES T2 (COL1, COL3) ON UPDATE RESTRICT  
)

Snowflake

Code

CREATE TABLE T1
(
    COL1 INTEGER CONSTRAINT CN1 PRIMARY KEY,
    COL2 INTEGER CONSTRAINT CN2 PRIMARY KEY ENFORCED,
    COL3 INTEGER CONSTRAINT CN3 PRIMARY KEY NOT ENFORCED,
    COL4 INTEGER CONSTRAINT CN4 PRIMARY KEY NOT ENFORCED
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--                                                         TRUSTED
                                                                ,
    COL5 INTEGER CONSTRAINT CN5 PRIMARY KEY NOT ENFORCED
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--                                                         NOT TRUSTED
                                                                    ,
    COL6 INTEGER CONSTRAINT CN6 PRIMARY KEY
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--                                            ENABLE QUERY OPTIMIZATION
                                                                     ,
    COL7 INTEGER CONSTRAINT CN7 PRIMARY KEY
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--                                            DISABLE QUERY OPTIMIZATION
                                                                      ,

    COL8 INTEGER CONSTRAINT CN8 UNIQUE,
    COL9 INTEGER
-- ** MSC-WARNING - MSCEWI1035 - CHECK STATEMENT NOT SUPPORTED **
--                 CONSTRAINT CN9 CHECK(COL9 < 3)
                                               ,

    COL10 INTEGER CONSTRAINT CN10 REFERENCES T2 (COL1, COL3),
    COL11 INTEGER CONSTRAINT CN11 REFERENCES T2 (COL1, COL3) ON DELETE CASCADE,
    COL12 INTEGER CONSTRAINT CN12 REFERENCES T2 (COL1, COL3) ON DELETE RESTRICT,
    COL13 INTEGER CONSTRAINT CN13 REFERENCES T2 (COL1, COL3) ON DELETE SET NULL,
    COL14 INTEGER CONSTRAINT CN14 REFERENCES T2 (COL1, COL3) ON DELETE NO ACTION,
    COL15 INTEGER CONSTRAINT CN15 REFERENCES T2 (COL1, COL3) ON UPDATE NO ACTION,
    COL16 INTEGER CONSTRAINT CN16 REFERENCES T2 (COL1, COL3) ON UPDATE RESTRICT
)

Known issues

CHECK Constraint Functionality is not supported in Snowflake.

Related EWIs

1. MSCEWI1073: Check Statement Not Supported.