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>
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>'
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
andDEFAULT
, 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.