In this Topic:

## 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> … TO ROLE and REVOKE <privileges> … FROM ROLE commands:

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

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


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>'


## 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.