Column Constraint Definition

Grammar Syntax

<column constraint definition> ::=
  [ <constraint name definition>  ] <column constraint>
  
<constraint name definition> ::=
  CONSTRAINT <constraint name>

<column constraint> ::=
    NOT NULL
  | <unique specification> 
  | <references specification> 
  | <check constraint definition> 
  
<unique specification> ::=
    UNIQUE
  | PRIMARY KEY
  
<references specification> ::=
  REFERENCES <referenced table and columns> 
      [ MATCH <match type>  ] [ <referential triggered action>  ]

<check constraint definition> ::=
     CHECK <left paren>  <search condition>  <right paren> 

Click here to go to the ANSI SQL Standard specification for this syntax.

Functional equivalence review in Snowflake is pending for some nodes used by this clause.

Sample Source Patterns

Sample with COLUMN NAME CONSTRAINT NOT NULL

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) CONSTRAINT SOMENAMEHERE NOT NULL
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) CONSTRAINT SOMENAMEHERE NOT NULL
);

Sample with COLUMN NAME UNIQUE

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) UNIQUE
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) UNIQUE
);

Sample with COLUMN NAME PRIMARY KEY

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) PRIMARY KEY
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) PRIMARY KEY
);

Sample with COLUMN NAME REFERENCES

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) REFERENCES SOMETABLE (SOMECOLUMN, SOMEOTHERCOLUMN)
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) REFERENCES SOMETABLE (SOMECOLUMN, SOMEOTHERCOLUMN)
);

Sample with COLUMN NAME NOT NULL CHECK

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) NOT NULL CHECK (SOMECOL > 1)
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   COLNAME VARCHAR(10) NOT NULL CHECK (SOMECOL > 1) /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CheckColumnConstraint' NODE ***/
);

Sample with CONSTRAINT NAME PRIMARY KEY

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME PRIMARY KEY
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME PRIMARY KEY
);

Sample with CONSTRAINT NAME UNIQUE

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME UNIQUE (SOMENAME)
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME UNIQUE (SOMENAME)
);

Sample with CONSTRAINT NAME FOREIGN KEY

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME FOREIGN KEY (SOMENAME) REFERENCES T MATCH SIMPLE
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME FOREIGN KEY (SOMENAME) REFERENCES T MATCH SIMPLE /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MatchMatchType' NODE ***/
);

Sample with CONSTRAINT NAME CHECK

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME CHECK(A > 1)
);

Snowflake Output Code

CREATE TABLE COLUMN_CONSTRAINTS (
   CONSTRAINT SOMENAME CHECK(A > 1 /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SearchCondition' NODE ***/)
);

Related EWIs

1. MSCEWI1073: Pending Functional Equivalence Review.