Problem
I have administrator responsibility for some legacy SQL Server databases that I did not create and do not use much personally. I have been charged with listing the SQL Server foreign keys within these databases. Can you present some different approaches to developing this kind of information as well as ways of validating the information generated by the different approaches?
Solution
It is relatively easy to find queries strewn across the Internet that claim to enumerate the foreign keys in a SQL Server database along with information about them. The best approach may depend on your personal preferences and needs as well as the type of databases for which you need to enumerate foreign keys.
For SQL Server databases, there are two common approaches to enumerating foreign keys.
- A traditional one is to use system tables that can enumerate tables with foreign keys as well as the tables that they reference.
- Queries based on INFORMATION_SCHEMA views are an alternative approach that may also be suitable for different types of databases than just SQL Server.
It is important to understand that foreign keys are defined in scripts; see this tip for sample T-SQL code and SSMS-based manual techniques for creating and specifying foreign keys. By examining the scripts for foreign keys in a database, you can verify how the foreign keys for a table are defined. While examining the scripts for individual tables can be tedious, it can also definitively verify a foreign key relationship. In addition, this kind of validation can give you helpful insights about how to define foreign keys in other databases.
A Quick Review of Foreign Keys
A foreign key is typically based on a set of one or more columns in a child table that references primary or unique key columns in a parent table. You can also define a foreign key relationship among two or more columns in the same table. In both scenarios, there is a referenced relationship so that the referencing column values match referenced column values. Also, referenced values cannot typically be removed from their columns without violating the foreign key relationship in a child set of column(s).
Foreign keys will always have names. The names are called foreign key constraints. You can designate the name for a foreign key constraint in a CREATE TABLE statement or ALTER TABLE statement with the CONSTRAINT keyword. You can use the CONSTRAINT keyword for naming a constraint at the same time that you create the constraint with the FOREIGN KEY and REFERENCES keywords. If you do not explicitly assign a foreign key name, then SQL SERVER will implicitly assign one. Well-constructed constraint names can provide useful information about foreign keys when they are enumerated.
SQL Server optionally allows action settings for designating how changes to referenced column values can impact referencing column values. A prior MSSQLTips.com article describes the syntax and operation of these action settings. For example, you can still maintain referential integrity when deleting a referenced column value with matching referencing column values if you specify an ON DELETE CASCADE option in the script for the foreign key. With this option, deleting a referenced column value in a parent set of columns also deletes all rows with matching values from a child (or referencing) set of columns. With the NO ACTION setting, neither updates nor deletes to values in the referenced columns are allowed if there are corresponding values in a referencing set of columns. The NO ACTION setting is enforced unless another action setting is explicitly designated.
For the purposes of this tip, constraint names are critical because they allow you to designate the foreign key relationships that the tip’s code enumerates; each foreign key must have its own distinct name. It is also imperative to understand that foreign keys denote a relationship between a child set of columns and a parent set of columns. Finally, you may find it useful to have a general understanding of foreign key cascading action settings to know what they designate when foreign keys are enumerated.
Enumerating all foreign key constraints for the AdventureWorksDW2014 database
The following scripts illustrate how to enumerate all foreign key constraints for the AdventureWorksDW2014 sample database. The approaches coded in this section give you an overview of all the foreign key constraints within a database.
The first two lines reference the sample database. The first code block illustrates a system tables approach to enumerating foreign keys for the database. The second code block illustrates an INFORMATION_SCHEMA approach to enumerating foreign keys for the database. The trailing ORDER BY statement in both first and second code blocks facilitates comparisons between the result set rows from both queries.
USE AdventureWorksDW2014;
GO
-- using sys tables to enumerate foreign keys
-- returns 45 constraint rows
SELECT
f.name constraint_name
,OBJECT_NAME(f.parent_object_id) referencing_table_name
,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
,OBJECT_NAME (f.referenced_object_id) referenced_table_name
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name
,delete_referential_action_desc
,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
ORDER BY f.name
-- using INFORMATION_SCHEMA to enumerate foreign keys
-- returns 45 constraint rows
SELECT
C.CONSTRAINT_NAME [constraint_name]
,C.TABLE_NAME [referencing_table_name]
,KCU.COLUMN_NAME [referencing_column_name]
,C2.TABLE_NAME [referenced_table_name]
,KCU2.COLUMN_NAME [referenced_column_name]
,RC.DELETE_RULE delete_referential_action_desc
,RC.UPDATE_RULE update_referential_action_desc
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY C.CONSTRAINT_NAME
The screen shot below shows the first 13 rows from the result set for each code block. Notice the two result set excerpts appear identical in content. Also, there is a total of 90 rows across both result sets – 45 each from the first and second blocks.
The FK_DimAccount_DimAccount constraint appears as the first row in both result sets. This constraint restricts values in the ParentAccountKey column to those in a previously input AccountKey column. The AccountKey column is the primary key for the DimAccount table, and therefore every row in the table has a unique AccountKey value. The ParentAccountKey value for a new row in the DimAccount table depends on the existence of a previously specified AccountKey value for another row in the same table. Any AccountKey value can have multiple rows depend on it through the ParentAccountKey value for the other rows. If an AccountKey value has one or more other rows with a ParentAccountKey value referencing it, then the referenced AccountKey value cannot be removed or changed in the table without corresponding changes in the ParentAccountKey values that reference it.
The first of the following two ALTER TABLE statements shows the code to define the FK_DimAccount_DimAccount constraint in the DimAccount table. Within the first ALTER TABLE statement, notice that the DimAccount table name appears in the ALTER TABLE clause as well as its REFERENCES clause. The second ALTER TABLE statement specifies a distinct check of the FK_DimAccount_DimAccount constraint outside of the preceding ALTER TABLE statement. This second distinct check can be especially relevant in cases where foreign key constraints are temporarily disabled and later re-enabled. For more detail on foreign key checking for disabled and re-enabled foreign keys (as well as related issues) see this summary.
ALTER TABLE [dbo].[DimAccount] WITH CHECK ADD CONSTRAINT [FK_DimAccount_DimAccount] FOREIGN KEY([ParentAccountKey])
REFERENCES [dbo].[DimAccount] ([AccountKey])
GO
ALTER TABLE [dbo].[DimAccount] CHECK CONSTRAINT [FK_DimAccount_DimAccount]
GO
The FK_DimCustomer_DimGeorgraphy constraint in the second row of the result sets below denotes a more typical type of foreign key. In this case, the referencing column of one table references a column in another table. The GeographyKey column from the DimCustomer table references the GeographyKey column from the DimGeography table. The GeorgraphyKey column serves as a primary key column in the DimGeography table and a foreign key column in the DimCustomer table. The FK_DimCustomer_DimGeorgraphy constraint forces referential integrity so that it is impossible to enter a new customer row in the DimCustomer table with a GeorgraphyKey value that does not already exist in the DimGeography table.
Here’s a pair of scripts for adding the FK_DimCustomer_DimGeography constraint to the DimCustomer table and enforcing the constraint for each inserted or changed row. Notice that the first ALTER TABLE statement in this script references two different tables – DimCustomer in the ALTER TABLE clause and DimGeography in the REFERENCES clause. The value after the CONSTRAINT keyword designates the name of the foreign key constraint. The FOREIGN KEY keyword specifies the referencing column in the DimCustomer referencing table. The table name after the REFERENCES keyword denotes the referenced table — DimGeography. The column name in parentheses after the table name denotes the referenced column — GeographyKey.
ALTER TABLE [dbo].[DimCustomer] WITH CHECK ADD CONSTRAINT [FK_DimCustomer_DimGeography] FOREIGN KEY([GeographyKey])
REFERENCES [dbo].[DimGeography] ([GeographyKey])
GO
ALTER TABLE [dbo].[DimCustomer] CHECK CONSTRAINT [FK_DimCustomer_DimGeography]
GO
The constraints on the first and second rows of a result sets below can exist for either OLTP or OLAP databases. However, OLAP databases, such as the AdventureWorksDW2014 database, typically rely heavily on foreign keys. The FACT tables in an OLAP database usually reference one or more DIM tables shared across multiple FACT tables. This forces the FACT tables to be referentially consistent for the DIM tables that they reference.
The thirteenth row in the following two result sets denotes a constraint for a FACT table, FactCurrencyRate that references a DIM table, DimCurrency. The CurrencyKey column in each row of the FactCurrencyRate table must exist in the CurrencyKey column of the DimCurrency table. The constraint denoting this foreign key relationship has the name FK_FactCurrencyRate_DimCurrency.
Enumerating foreign key constraints for a designated referenced table
The following scripts illustrate how to enumerate a select set of foreign key constraints that are dependent on a particular referenced table. The first approach demonstrates the sys table syntax and the second approach reveals INFORMATION_SCHEMA syntax. This example uses the AdventureWorks2014 database, which is an OLTP database in contrast to the OLAP database in the preceding example. Both OLTP and OLAP databases take advantage of the same approach for enumerating all foreign key constraints. To select a subset of all foreign key constraints for a particular referenced table, all you need to do is add a WHERE clause designating the referenced table name.
With just the default NO ACTION setting, there is mutual constraining between referenced and referencing tables in a foreign key relationship. Referencing tables cannot accept a new referencing column value that is not already a primary key value or a unique key value in the referenced table. Similarly, referenced tables cannot change a referenced column value that is in a referencing table; to do so would violate the integrity of the foreign key. So long as the changes that you want to make to a referenced table are not among those in the referencing columns, then you can change them in the referenced table without any concern for impact on the foreign key integrity.
By using cascading delete and update settings, you can make changes to a referenced table even when a referencing table shares referencing column values with a referenced table. Cascading delete or update referential action settings can propagate actions made to a referenced table to referencing tables to maintain the integrity of foreign key references.
USE AdventureWorks2014
GO
-- using sys tables to enumerate foreign keys
-- for a particular referenced table
SELECT
f.name constraint_name
,OBJECT_NAME(f.parent_object_id) referencing_table_name
,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
,OBJECT_NAME (f.referenced_object_id) referenced_table_name
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name
,delete_referential_action_desc
,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = 'SalesOrderHeader'
-- using INFORMATION_SCHEMA to enumerate foreign keys
-- for a particular referenced table
SELECT
C.CONSTRAINT_NAME [constraint_name]
,C.TABLE_NAME [referencing_table_name]
,KCU.COLUMN_NAME [referencing_column_name]
,C2.TABLE_NAME [referenced_table_name]
,KCU2.COLUMN_NAME [referenced_column_name]
,RC.DELETE_RULE delete_referential_action_desc
,RC.UPDATE_RULE update_referential_action_desc
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND C2.TABLE_NAME = 'SalesOrderHeader'
The following screen shot displays the output from each of the two approaches for listing referencing tables for the SalesOrderHeader table in the AdventureWorks2014 database. The two tables that reference the SalesOrderHeader table are the SalesOrderDetail table and the SalesOrderHeaderSalesReason table. Cascading delete actions are specified for both referencing tables. The cascading delete action setting means that any SalesOrderID value deleted from the SalesOrderHeader table will result in deleted rows from both SalesOrderDetail and SalesOrderHeaderSalesReason tables for rows with a matching SalesOrderID value. In contrast, no cascading action is specified for updates. Therefore, any attempt to update a SalesOrderID value in the SalesOrderHeader table will be prohibited for foreign key check reasons if that SalesOrderID value also exists in either the SalesOrderDetail or SalesOrderHeaderSalesReason tables.
The following ALTER TABLE statements illustrate the syntax for denoting the foreign key relationship of the SalesOrderDetail table to the SalesOrderHeader table. Notice that the first ALTER TABLE statement includes an ON DELETE CASCADE clause. With this clause, if a SalesOrderID value is deleted from the SalesOrderHeader table, then all rows in the SalesOrderDetail table with a matching SalesOrderID value will also be deleted.
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
GO
No cascading update action is specified along with the preceding ON DELETE CASCADE setting. SQL Server does permit an ON UPDATE CASCADE clause, but you will notice that it was not used in this example. Many developers argue you should not update primary key values. In this example, that rule makes sense since SalesOrderID values in the SalesOrderHeader table are identity values. When the primary key for the referenced table is a “natural” value that is not an arbitrary number but has some meaning for the row, such as product name type, then the use of the ON UPDATE CASCADE clause may be appropriate as the number of product types expand. For example, the same product can belong to different product types before and after a product line expansion and reorganization.
Next Steps
- Try the code samples in this tip for listing all foreign keys or a subset of foreign keys with your own databases. Focus on thinking about foreign keys as referencing columns from referencing tables that point at referenced columns from referenced tables.
- Keep in mind that referenced and referencing column names can have different names. This is necessarily the case when the referencing column and referenced column are from the same table. When the referencing table is different from the referenced table, the referencing column and referenced column can have the same or different names. The code examples in this tip do not require referencing column names to match referenced column names.
- When time permits, I encourage you to script tables participating in foreign key relationships. This can help you understand how they are used in an application. Additionally, it may give you insights about best practices and common practices within an organization for using foreign keys.
- This tip closes with a reminder that foreign keys are an advanced database development topic. This tip narrowly focuses on techniques for enumerating foreign keys within a database. Other foreign key topics are covered only to help you understand the columns of results sets for enumerated foreign keys within a database. Furthermore, there are many foreign key issues that are not covered at all within this tip. Hopefully, this tip will motivate others to author additional tips on foreign keys issues not covered here.
