Modifying Constraints

Once a constraint is created:

  • The constraint can be renamed.
  • Some properties can be modified, e.g. ENFORCED and VALIDATE.
  • Some properties cannot be modified, e.g. such as DEFERRABLE. To modify these properties, the constraint must be dropped and recreated.
  • The column definition for a constraint cannot be modified, e.g. add new columns, drop existing columns, or change the order of columns. To make these types of changes, the constraint must be dropped and recreated.

When modifying a constraint, the constraint can be identified using either the constraint name or the columns in the constraint definition along with the type of the constraint. Primary keys can also be identified using the PRIMARY KEY keyword, because each table can have only a single primary key.

If a table with constraints is modified, e.g. rename table or swap table with another table, the constraints are updated to reflect the changes.

In this Topic:

Renaming a Constraint

Use the following syntax for the ALTER TABLE command to rename a constraint:

ALTER TABLE <table_name> RENAME CONSTRAINT <old_name> TO <new_name>;

Modifying Properties of a Constraint

Use the following syntax for the ALTER TABLE command to modify the properties of a constraint:

ALTER TABLE <table_name>
    { ALTER | MODIFY } { CONSTRAINT <name> | PRIMARY KEY | { UNIQUE | FOREIGN KEY } (<column_name>, [ ... ] ) }
    { [ [ NOT ] ENFORCED ] [ VALIDATE | NOVALIDATE ] };

Currently, Snowflake only supports setting the constraints to the default values:

NOT ENFORCED NOVALIDATE

For descriptions of the constraint properties, see Constraint Properties.

Modifying a Table with Constraints

If a table with constraints is renamed, the constraints for the table, as well as any foreign key constraints that reference the table are updated to reference the new name.

Likewise, if a table is swapped with another, existing table, all the constraints on the table are maintained on the swapped table.

For more details about renaming or swapping tables, see ALTER TABLE.