SYS.FOREIGN_KEYS
Some parts in the output code are omitted for clarity reasons.
Description
Contains a row per object that is a FOREIGN KEY constraint (SQLServer Documentation).
The columns for FOREIGN KEY (sys.foreign_keys) are the following:
Column name | Data type | Description | Has equivalent column in Snowflake |
---|---|---|---|
<Columns inherited from sys.objects> | - | For a list of columns that this view inherits, see sys.objects (Transact-SQL). | Partial |
referenced_object_id | int | ID of the referenced object. | No |
key_index_id | int | ID of the key index within the referenced object. | No |
is_disabled | bit | FOREIGN KEY constraint is disabled. | No |
is_not_for_replication | bit | FOREIGN KEY constraint was created by using the NOT FOR REPLICATION option. | No |
is_not_trusted | bit | FOREIGN KEY constraint has not been verified by the system. | No |
delete_referential_action | tinyint | The referential action that was declared for this FOREIGN KEY when a delete happens. See SQLServer Documentation. | No |
delete_referential_action_desc | nvarchar(60) | Description of the referential action that was declared for this FOREIGN KEY when a delete occurs. See SQLServer Documentation. | No |
update_referential_action | tinyint | The referential action that was declared for this FOREIGN KEY when an update happens. See SQLServer Documentation. | No |
update_referential_action_desc | nvarchar(60) | Description of the referential action that was declared for this FOREIGN KEY when an update happens. See SQLServer Documentation. | No |
is_system_named | bit | 1 = Name was generated by the system. 0 = Name was supplied by the user. | No |
The inherited columns from sys.objects are the following:
For more information, review the sys.objects documentation.
Column name | Data type | Description | Has equivalent column in Snowflake |
---|---|---|---|
name | sysname | Object name. | Yes |
object_id | int | Object identification number. Is unique within a database. | No |
principal_id | int | ID of the individual owner, if different from the schema owner. | No |
schema_id | int | ID of the schema that the object is contained in. | No |
parent_object_id | int | ID of the object to which this object belongs. | No |
type | char(2) | Object type | Yes |
type_desc | nvarchar(60) | Description of the object type | Yes |
create_date | datetime | Date the object was created. | Yes |
modify_date | datetime | Date the object was last modified by using an ALTER statement. | Yes |
is_ms_shipped | bit | Object is created by an internal SQL Server component. | No |
is_published | bit | Object is created by an internal SQL Server component. | No |
is_schema_published | bit | Only the schema of the object is published. | No |
Notice that, in this case, for the sys.foreign_keys, there is no equivalence in Snowflake. But, the equivalence is made under the columns inherited from sys.objects.
Applicable column equivalence
SQLServer | Snowflake | Limitations | Applicable |
---|---|---|---|
name | CONSTRAINT_NAME | Names auto-generated by the database may be reviewed to the target Snowflake auto-generated name, | Yes |
type | CONSTRAINT_TYPE | The type column has a variety of options. But, in this case, the support is only for the letter 'F' which represents the foreign keys. | No. Because of the extra validation to determine the foreign keys from all table constraints, it is not applicable. |
type_desc | CONSTRAINT_TYPE | No limitions found. | No. Because of the extra validation to determine the foreign keys from all table constraints, it is not applicable. |
create_date | CREATED | Data type differences. | Yes |
modify_date | LAST_ALTERED | Data type differences. | Yes |
parent_object_id | CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME | Columns are generated only for the cases that use the OBJECT_ID() function and, the name has a valid pattern. | Yes |
Syntax in SQL Server
Syntax in Snowflake
Since the equivalence for the system foreign keys is the catalog view in Snowflake for in ormation_schema.table_constraints, it is necessary to define the type of the constraint in an additional 'WHERE' clause to identify foreign key constraints from other constraints.