ADD Table Constraint

Description

This form adds a new constraint to a table using the same constraint syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints.

The NOT VALID clause is not supported in Snowflake.

The functionality of the Add table constraint is going to depend on the definition of the constraint, for more information on table constraints and their translation click here.

Click here to navigate to the PostgreSQL documentation page for this syntax.

Grammar Syntax

ADD table_constraint [ NOT VALID ]

table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Sample Source Patterns

PostgreSQL

Check

ALTER TABLE ONLY Table1
ADD CONSTRAINT const CHECK (char_length(code) = 5);

Unique

ALTER TABLE ONLY Table1
ADD CONSTRAINT const1 UNIQUE (id),
ADD CONSTRAINT const2 UNIQUE (code) INCLUDE ( column_name);

Primary key

ALTER TABLE Table1
ADD CONSTRAINT const1 PRIMARY KEY (code),
ADD CONSTRAINT const2 PRIMARY KEY (id) WITH (par1 = val1, par2 = val2);

Exclude

ALTER TABLE Table1 *
ADD CONSTRAINT const1 EXCLUDE USING gist (c WITH &&);

Foreign key

ALTER TABLE ONLY Table1
ADD CONSTRAINT const1 FOREIGN KEY (address) REFERENCES addresses (address2) MATCH FULL,
ADD CONSTRAINT const2 FOREIGN KEY (code) REFERENCES codes (code2) ON DELETE CASCADE,

Not Valid

ALTER TABLE Table1 *
ADD CONSTRAINT const UNIQUE (zip) NOT VALID;

Snowflake

ALTER TABLE Table1
ADD CONSTRAINT const UNIQUE (zip)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. NOT VALID **
--                                  NOT VALID
                                           ;

Check

-- ** MSC-WARNING - MSCEWI1035 - CHECK STATEMENT NOT SUPPORTED **
--ALTER TABLE ONLY Table1
--ADD CONSTRAINT const CHECK (char_length(code) = 5)
                                                  ;

Unique

ALTER TABLE Table1
ADD CONSTRAINT const1 UNIQUE (id);
ALTER TABLE Table1
ADD CONSTRAINT const2 UNIQUE (code)
-- ** MSC-WARNING - MSC-PG0007 - INCLUDE PARAMETER NOT APPLICABLE. CONSTRAINT INDEX PARAMETERS ARE NOT SUPPORTED IN SNOWFLAKE. **
--                                    INCLUDE ( column_name)
                                                          ;

Primary key

--** MSC-WARNING - MSC-PG0008 - TABLE INHERITANCE IS NOT SUPPORTED IN SNOWFLAKE. **
ALTER TABLE Table1
ADD CONSTRAINT const1 PRIMARY KEY (code);
ALTER TABLE Table1
ADD CONSTRAINT const2 PRIMARY KEY (id)
-- ** MSC-WARNING - MSC-PG0007 - WITH PARAMETER NOT APPLICABLE. CONSTRAINT INDEX PARAMETERS ARE NOT SUPPORTED IN SNOWFLAKE. **
--                                       WITH (par1 = val1, par2 = val2)
                                                                      ;

Exclude

-- ** MSC-ERROR - MSC-PG0009 - EXCLUDE CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE. **
--ALTER TABLE Table1 *
--ADD CONSTRAINT const1 EXCLUDE USING gist (c WITH &&)
                                                    ;

Foreign key

ALTER TABLE Table1
ADD CONSTRAINT const1 FOREIGN KEY (address) REFERENCES addresses (address2) MATCH FULL ;
ALTER TABLE Table1
ADD CONSTRAINT const2 FOREIGN KEY (code) REFERENCES codes (code2) ON DELETE CASCADE ;

Not Valid

ALTER TABLE Table1
ADD CONSTRAINT const UNIQUE (zip)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. NOT VALID **
--                                  NOT VALID
                                           ;

Related EWIs

1. MSCEWI1002: Removed next statement, not applicable in SnowFlake.
2. MSCEWI1035: Check statement not supported.
3. MSC-PG0007: Constraint index parameter not supported.
4. MSC-PG0008: Inheritance not supported.
5. MSC-PG0009: Exclude constraint not supported.