Additional Constraint Details¶
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.
GRANT REFERENCES ON TABLE <pk_table_name> TO <role_name> REVOKE REFERENCES ON TABLE <pk_table_name> FROM ROLE <role_name>
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.