SYS.FOREIGN_KEYS
Last updated
Last updated
Contains a row per object that is a FOREIGN KEY constraint ().
The columns for FOREIGN KEY (sys.foreign_keys) are the following:
<Columns inherited from sys.objects>
-
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
No
delete_referential_action_desc
nvarchar(60)
No
update_referential_action
tinyint
No
update_referential_action_desc
nvarchar(60)
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:
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.
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
Results differ due to the differences in column objects and missing equivalence. The result may be checked.
In this example, a database and schema were created to exemplify the processing of the names to create different and equivalent columns.
If the name coming inside the OBJECT_ID() function does not have a valid pattern, it will not be converted due to name processing limitations on special characters.
Review the database that is being used in Snowflake.
The 'F' in SQL Server means 'Foreign Key' and it is removed due to the validation at the ending to specify the foreign key from all the table constraints.
The 'type_desc' column is removed due to the validation at the ending to specify the foreign key from all the table constraints.
The following example shows a more complex scenario where the columns from sys.foreign_keys (inherited from sys.objects) are inside a function DATEDIFF. In this case, the argument corresponding to the applicable equivalence is changed to the corresponding column from the information.schema in Snowflake.
The result may change if the creation date is specific due to the time on which the queries were executed. It is possible to execute a specified query at one time on the origin database and then execute the objects at another time in the new Snowflake queries.
If the referenced name is one auto-generated from the database, it would be probable to review it and use the wanted value.
Since the columns from sys.foreign_keys are not completely equivalent in Snowflake, some results may change due to the limitations on the equivalence.
Based on the name that receives the OBJECT_ID() function, the processing of this name will be limited and dependent on formatting.
Since the OBJECT_NAME() function is not supported yet, the transformations related to this function are not supported.
For a list of columns that this view inherits, see
The referential action that was declared for this FOREIGN KEY when a delete happens. See .
Description of the referential action that was declared for this FOREIGN KEY when a delete occurs. See .
The referential action that was declared for this FOREIGN KEY when an update happens. See .
Description of the referential action that was declared for this FOREIGN KEY when an update happens. See .
For more information, review the .
To accomplish correctly the following samples (except ), it is required to run the following statements:
This translation may require verification if the constraint name is auto-generated by the database and used in the query. For more information review the section.
: Pending Functional Equivalence Review.