Additional Constraint Details

Security Privileges for Constraints

For creating primary key or unique constraints:

  • When altering an existing table to add the constraint, the current role for the user must have the OWNERSHIP privilege on the table.

  • When creating a new table, the current role for the user must have the CREATE TABLE privilege on the schema where the table will be created.

For creating foreign key constraints:

  • The current role for the user must have the OWNERSHIP privilege on the foreign key table.

  • The current role for the user must have the REFERENCES privilege on the unique/primary key table.

The REFERENCES privilege can be granted to and revoked from roles using the GRANT <privileges> and REVOKE <privileges> commands:

GRANT REFERENCES ON TABLE <pk_table_name> TO ROLE <role_name>

REVOKE REFERENCES ON TABLE <pk_table_name> FROM ROLE <role_name>
Copy

Comments for Constraints

Similar to other database objects and constructs, Snowflake supports providing comments for constraints. Comments can be added to constraints in two ways:

  • Out-of-line constraint support the COMMENT clause, in which a comment can be specified while defining the constraint.

    Comments are not allowed when defining constraints inline because the comments may cause ambiguity.

  • Comments can also be set on constraints using the COMMENT command:

    COMMENT [IF EXISTS] ON CONSTRAINT <constraint_name> IS '<comment_string>'
    Copy

Support for Constraints in Copy Commands

Snowflake supports creating copies of tables using CREATE TABLE:

  • To create an empty copy, use CREATE TABLE … LIKE.

  • To create a clone, use CREATE TABLE … CLONE.

In addition, copies of tables are automatically created when a schema or database is cloned.

Regardless of how a copy is created for a table, the constraints on the original table are also copied. When copying a foreign key with a referencing table (foreign key table) and a referenced table (primary key table), the following scenarios may occur:

  • If both tables are copied in the same command (such as during cloning of a schema or database), then a new foreign key is created between the new referencing table and the referenced table.

  • If only the referencing table is copied, then a new foreign key is created on the referencing table, which points to the original primary key table as the referenced table.

  • If only the referenced table is copied, no new foreign keys are created, although the primary/unique keys are copied.

As a result, if a user copies a referencing and referenced table separately, they must manually create a new foreign key, or change the primary key table for the new foreign key manually.

Support for Constraints in GET_DDL

Snowflake supports constraints for GET_DDL; however, note the following:

  • Single-column only constraints, such as NOT NULL and DEFAULT, are reconstructed inline with the definition of the column.

  • Table constraints, such as unique/primary/foreign keys, are always reconstructed as out-of-line constraints, even if they consist of a single column.

  • For unnamed constraints (i.e. constraints with a system-generated name), the system-generated name is not returned by GET_DDL.

Language: English