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 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

SQLServerSnowflakeLimitationsApplicable

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

IN -> SqlServer_01.sql
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