SYS.FOREIGN_KEYS

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 nameData typeDescriptionHas 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 nameData typeDescriptionHas 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 SnowflakeLimitationsApplicable

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

SELECT ('column_name' | * )
FROM sys.foreign_keys;

Syntax in Snowflake

SELECT ('column_name' | * )
FROM information_schema.table_constraints 
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

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.

Sample Source Patterns

To accomplish correctly the following samples (except pattern number 3), it is required to run the following statements:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Name_Test FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);


INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (101, 1, '2023-09-01', 100.50),
    (102, 1, '2023-09-02', 75.25),
    (103, 2, '2023-09-03', 50.00);

1. Simple Select Case

SQL Server

SELECT *
FROM sys.foreign_keys;

Snowflake

SELECT * 
FROM information_schema.table_constraints 
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

Results differ due to the differences in column objects and missing equivalence. The result may be checked.

2. Name Column Case

SQL Server

SELECT * FROM sys.foreign_keys WHERE name = 'FK_Name_Test';

Snowflake

SELECT
       *
FROM
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
       CONSTRAINT_NAME = 'FK_Name_Test'
       AND CONSTRAINT_TYPE = 'FOREIGN KEY';

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 Know Issues section.

3. Parent Object ID Case

In this example, a database and schema were created to exemplify the processing of the names to create different and equivalent columns.

SQL Server

use database_name_test
create schema schema_name_test

CREATE TABLE schema_name_test.Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

CREATE TABLE schema_name_test.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Name_Test FOREIGN KEY (CustomerID) REFERENCES schema_name_test.Customers(CustomerID)
);

INSERT INTO schema_name_test.Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO schema_name_test.Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (101, 1, '2023-09-01', 100.50),
    (102, 1, '2023-09-02', 75.25),
    (103, 2, '2023-09-03', 50.00);

SELECT * FROM sys.foreign_keys WHERE name = 'FK_Name_Test' AND parent_object_id = OBJECT_ID(N'database_name_test.schema_name_test.Orders')

Snowflake

----** MSC-WARNING - MSCEWI4001 - Transformation for USE <database> is disabled by default. **
--use database_name_test

create schema schema_name_test;

CREATE OR REPLACE TABLE schema_name_test.Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

CREATE OR REPLACE TABLE schema_name_test.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Name_Test FOREIGN KEY (CustomerID) REFERENCES schema_name_test.Customers (CustomerID)
);

INSERT INTO schema_name_test.Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO schema_name_test.Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (101, 1, '2023-09-01', 100.50),
    (102, 1, '2023-09-02', 75.25),
    (103, 2, '2023-09-03', 50.00);

SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_NAME = 'FK_NAME_TEST'
    AND CONSTRAINT_CATALOG = 'DATABASE_NAME_TEST'
    AND CONSTRAINT_SCHEMA = 'SCHEMA_NAME_TEST'
    AND TABLE_NAME = 'ORDERS'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';

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.

4. Type Column Case

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.

SQL Server

SELECT * FROM sys.foreign_keys WHERE type = 'F';

Snowflake

SELECT
*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
----** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONSTRAINT_TYPE COLUMN ADDED AS EQUIVALENCE. **
--type = 'F'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';

5. Type Desc Column Case

The 'type_desc' column is removed due to the validation at the ending to specify the foreign key from all the table constraints.

SQL Server

SELECT * 
FROM sys.foreign_keys 
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT';

Snowflake

SELECT
*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
----** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONSTRAINT_TYPE COLUMN ADDED AS EQUIVALENCE. **
--type_desc = 'FOREIGN_KEY_CONSTRAINT'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';

6. Modify Date Column Simple Case

SQL Server

SELECT *
FROM sys.foreign_keys
WHERE modify_date = CURRENT_TIMESTAMP;

Snowflake

SELECT
*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
LAST_ALTERED = CURRENT_TIMESTAMP
AND CONSTRAINT_TYPE = 'FOREIGN KEY';

7. Modify Date Column with DATEDIFF() Case

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.

SQL Server

SELECT *
FROM sys.foreign_keys
WHERE DATEDIFF(DAY, modify_date, GETDATE()) <= 30;

Snowflake

SELECT
*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
DATEDIFF(DAY, LAST_ALTERED, CURRENT_TIMESTAMP() :: TIMESTAMP) <= 30
AND CONSTRAINT_TYPE = 'FOREIGN KEY';

8. Create Date Column Case

SQL Server

SELECT *
FROM sys.foreign_keys
WHERE create_date = '2023-09-12 14:36:38.060';

Snowflake

SELECT
*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CREATED = '2023-09-12 14:36:38.060'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';

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.

9. Selected Columns Single Name Case

SQL Server

SELECT name
FROM sys.foreign_keys;

Snowflake

SELECT
CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY';

10. Selected Columns Qualified Name Case

SQL Server

SELECT
    fk.name
FROM sys.foreign_keys AS fk;

Snowflake

SELECT fk.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS fk
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

Known Issues

1. The 'name' column may not show a correct output if the constraint does not have a user-created name

If the referenced name is one auto-generated from the database, it would be probable to review it and use the wanted value.

2. When selecting columns, there is a limitation that depends on the applicable columns that are equivalent in Snowflake

Since the columns from sys.foreign_keys are not completely equivalent in Snowflake, some results may change due to the limitations on the equivalence.

3. The OBJECT_ID() function may have a valid pattern to be processed or the database, schema or table could not be extracted

Based on the name that receives the OBJECT_ID() function, the processing of this name will be limited and dependent on formatting.

4. Name Column With OBJECT_NAME() Function Case

Since the OBJECT_NAME() function is not supported yet, the transformations related to this function are not supported.

SELECT name AS ForeignKeyName,
       OBJECT_NAME(parent_object_id) AS ReferencingTable,
       OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys;

5. SCHEMA_NAME() and TYPE_NAME() functions are also not supported yet.

6. Different Join statement types may be not supported if the system table is not supported. Review the supported system tables here.

7. Cases with JOIN statements are not supported.

8. Names with alias AS are not supported.

Related EWIs

  1. MSCEWI4046: System table is not supported in Snowflake.

PreviousSystem TablesNextQueries

Last updated