Constraint Definition

An expression that defines a table constraint

Grammar syntax

constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

Sample Source

1. Primary Key Table Constraint

Primary Key is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer,
    PRIMARY KEY (col1) DISABLE NOVALIDATE RELY
);

2. Foreign Key Table Constraint

Foreign Key is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer,
    CONSTRAINT fk_col1 FOREIGN KEY (col1) REFERENCES tbl1(col1) DISABLE NOVALIDATE 
);

3. Unique Table Constraint

Foreign Key is fully supported by Snowflake.

The syntax is equivalent in both languages.

Sample Source

CREATE TABLE table1 (
    col1 integer,
    CONSTRAINT c1_unique UNIQUE(col1) DISABLE NOVALIDATE 
);

4. Check Table Constraint

Check constraint is not supported by Snowflake.

Sample Source

Hive

CREATE TABLE table1 (
    price double,
    CONSTRAINT c1_check CHECK (price > 0 AND price <= 1000)
);

Snowflake

CREATE TABLE table1 (
    price double 
    --** MSC-ERROR - MSC-HVXXXX - THE CREATE TABLE CHECK CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE. **
    --CONSTRAINT c1_check CHECK (price > 0 AND price <= 1000)
);

Known Issues

No issues were found.

Related EWIs

1. MSC-HVXXXX : THE CREATE TABLE CHECK CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE.