Learn through the super-clean Baeldung Pro experience:
>> Membership and Baeldung Pro.
No ads, dark-mode and 6 months free of IntelliJ Idea Ultimate to start with.
1. Overview
In a relational database, foreign keys are crucial in maintaining referential integrity. In particular, they establish a link between tables, ensuring that the values in one table correspond to values in another.
This relationship helps maintain consistency across the database and prevents actions that could lead to orphaned records or invalid data. In SQL, understanding and listing foreign keys within a table is essential for database design, analysis, and troubleshooting.
In this tutorial, we’ll explore various ways to list foreign keys in SQL across different database systems, namely SQL Server, PostgreSQL and MySQL. Notably, we’ll use the Baeldung University database for illustration purposes.
2. Implementation in SQL Server
SQL Server provides multiple approaches for listing foreign keys in a database. We can retrieve this information using system-stored procedures, INFORMATION_SCHEMA views, and system catalog views.
Let’s explore these methods in detail.
2.1. Using sp_fkeys Stored Procedure
The sp_fkeys stored procedure is a system-defined stored procedure in SQL Server. It returns information about foreign keys in a specific table or a set of related tables. Furthermore, it’s one of the quickest ways to retrieve foreign key information.
For example, let’s list the foreign keys of the Registration table from the Baeldung University database:
EXEC sp_fkeys @pktable_name = 'Registration';
PKTABLE_QUALIFIER PKTABLE_OWNER PKTABLE_NAME PKCOLUMN_NAME FKTABLE_QUALIFIER FKTABLE_OWNER FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY
---------------- -------------- ------------- -------------- ----------------- ------------- ------------- -------------- ------- ----------- ----------- ------------------------ ------------------------ --------------
UniversityDB dbo Course id UniversityDB dbo Registration course_id 1 1 1 registration_course_id_fk PK_Course_id 7
UniversityDB dbo Student id UniversityDB dbo Registration student_id 1 1 1 registration_student_id_fk PK_Student_id 7
In this output, the sp_fkeys procedure provides details on the foreign key relationships. The results include the primary key table (PKTABLE_NAME), the foreign key table (FKTABLE_NAME), and the corresponding columns involved in these relationships. The FK_NAME column shows the foreign key constraint names, which can be useful for further database analysis.
2.2. Using INFORMATION_SCHEMA Views
The INFORMATION_SCHEMA views are another effective way to retrieve foreign key information in SQL Server.
In particular, these views are part of the SQL standard and provide detailed metadata about database objects, including foreign keys.
For example, let’s use the INFORMATION_SCHEMA views to list the foreign keys for the Registration table:
SELECT
FK.TABLE_NAME AS foreign_key_table,
CU.COLUMN_NAME AS foreign_key_column,
PK.TABLE_NAME AS referenced_table,
PT.COLUMN_NAME AS referenced_column,
C.CONSTRAINT_NAME AS constraint_name
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE
FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND FK.TABLE_NAME = 'Registration';
foreign_key_table foreign_key_column referenced_table referenced_column constraint_name
----------------- ------------------ ---------------- ---------------- ----------------------------
Registration course_id Course id registration_course_id_fk
Registration student_id Student id registration_student_id_fk
The query joins multiple INFORMATION_SCHEMA views to retrieve details about foreign keys.
The results show the foreign key table and column (foreign_key_table and foreign_key_column). Additionally, it also shows the referenced table and column (referenced_table and referenced_column), and the constraint name (constraint_name).
This method is flexible and allows for more complex filtering and querying of metadata.
3. Implementation in PostgreSQL
In PostgreSQL, there are two effective methods to list foreign keys: using the INFORMATION_SCHEMA views and querying the pg_constraint catalog. Each method provides different insights into the foreign key relationships within the database.
3.1. Using INFORMATION_SCHEMA Views
PostgreSQL’s INFORMATION_SCHEMA is a standard schema that offers detailed metadata about database objects, including foreign keys.
For example, let’s use a query to retrieve foreign key details for the Registration table:
SELECT
tc.table_name AS foreign_key_table,
kcu.column_name AS foreign_key_column,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column
FROM
information_schema.table_constraints AS tc
JOIN
information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN
information_schema.constraint_column_usage AS ccu
ON kcu.constraint_name = ccu.constraint_name
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'registration';
+-------------------+--------------------+------------------+-------------------+
| foreign_key_table | foreign_key_column | referenced_table | referenced_column |
|-------------------+--------------------+------------------+-------------------|
| registration | course_id | course | id |
| registration | student_id | student | id |
+-------------------+--------------------+------------------+-------------------+
SELECT 2
Time: 0.014s
Here, the query joins the information_schema.table_constraints, information_schema.key_column_usage, and information_schema.constraint_column_usage views. By doing so, it retrieves the foreign key table and column, along with the referenced table and column, specifically for the Registration table.
Eventually, it shows a clear view of the foreign keys and how the Registration table interacts with other tables.
Additionally, we can extend this to multiple tables by modifying the WHERE clause of the previous query:
SELECT
tc.table_name AS foreign_key_table,
kcu.column_name AS foreign_key_column,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column
FROM
information_schema.table_constraints AS tc
JOIN
information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN
information_schema.constraint_column_usage AS ccu
ON kcu.constraint_name = ccu.constraint_name
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name IN ('registration', 'exam');
+-------------------+--------------------+------------------+-------------------+
| foreign_key_table | foreign_key_column | referenced_table | referenced_column |
|-------------------+--------------------+------------------+-------------------|
| registration | course_id | course | id |
| registration | student_id | student | id |
| exam | student_id | student | id |
| exam | course_id | course | id |
+-------------------+--------------------+------------------+-------------------+
SELECT 4
Time: 0.014sThe updated query shows the foreign key for both the Registration and Exam tables by specifying multiple table names in the WHERE clause. As a result, we obtain a comprehensive view of how these tables are interrelated through foreign keys.
3.2. Using pg_constraint Catalog
Alternatively, PostgreSQL’s pg_constraint catalog provides a detailed, system-level view of constraints, including foreign keys.
For example, let’s retrieve the foreign keys for the Program table:
SELECT
conname AS constraint_name,
conrelid::regclass AS foreign_key_table,
a.attname AS foreign_key_column,
confrelid::regclass AS referenced_table,
af.attname AS referenced_column
FROM
pg_constraint AS c
JOIN
pg_attribute AS a
ON a.attnum = ANY(c.conkey)
AND a.attrelid = c.conrelid
JOIN
pg_attribute AS af
ON af.attnum = ANY(c.confkey)
AND af.attrelid = c.confrelid
WHERE
c.contype = 'f'
AND c.conrelid = 'program'::regclass;
+----------------------------+-------------------+--------------------+------------------+-------------------+
| constraint_name | foreign_key_table | foreign_key_column | referenced_table | referenced_column |
|----------------------------+-------------------+--------------------+------------------+-------------------|
| program_department_id_fkey | program | department_id | department | id |
+----------------------------+-------------------+--------------------+------------------+-------------------+
SELECT 1
Time: 0.007sIn this query, the pg_constraint catalog is joined with the pg_attribute table to map foreign key columns to their referenced columns. By filtering on c.contype = ‘f’, we focus on foreign keys, and by specifying c.conrelid = ‘program’::regclass, we target the Program table.
Furthermore, this method provides a detailed view of foreign key constraints, including their names and the specific columns involved.
Additionally, let’s retrieve the foreign key from multiple tables by modifying the WHERE clause:
SELECT
conname AS constraint_name,
conrelid::regclass AS foreign_key_table,
a.attname AS foreign_key_column,
confrelid::regclass AS referenced_table,
af.attname AS referenced_column
FROM
pg_constraint AS c
JOIN
pg_attribute AS a
ON a.attnum = ANY(c.conkey)
AND a.attrelid = c.conrelid
JOIN
pg_attribute AS af
ON af.attnum = ANY(c.confkey)
AND af.attrelid = c.confrelid
WHERE
c.contype = 'f'
AND conrelid IN ('faculty'::regclass, 'program'::regclass);
+----------------------------+-------------------+--------------------+------------------+-------------------+
| constraint_name | foreign_key_table | foreign_key_column | referenced_table | referenced_column |
|----------------------------+-------------------+--------------------+------------------+-------------------|
| program_department_id_fkey | program | department_id | department | id |
| faculty_department_id_fkey | faculty | department_id | department | id |
+----------------------------+-------------------+--------------------+------------------+-------------------+
SELECT 2
Time: 0.008sThis query retrieves foreign key constraints for both tables, Faculty and Program, thus offering a broader view of the relationships.
4. Implementation in MySQL
In MySQL, listing foreign keys can be accomplished using either the INFORMATION_SCHEMA views or the SHOW CREATE TABLE statement. Each method offers a unique perspective on the foreign key relationships within our database.
4.1. Using INFORMATION_SCHEMA Views
For example, let’s retrieve details about the foreign keys for the Registration table:
SELECT
table_name AS foreign_key_table,
column_name AS foreign_key_column,
referenced_table_name AS referenced_table,
referenced_column_name AS referenced_column
FROM
information_schema.key_column_usage
WHERE
constraint_schema = 'University'
AND table_name = 'Registration'
AND referenced_table_name IS NOT NULL;
+-------------------+--------------------+------------------+-------------------+
| foreign_key_table | foreign_key_column | referenced_table | referenced_column |
+-------------------+--------------------+------------------+-------------------+
| Registration | course_id | Course | id |
| Registration | student_id | Student | id |
+-------------------+--------------------+------------------+-------------------+
2 rows in set (0.013 sec)
This query extracts foreign key information from the information_schema.key_column_usage view. By filtering on the constraint_schema (in this case, the University database) and the table_name (Registration), we focus on the relevant foreign key relationships.
The query lists the foreign key table and column, along with the referenced table and column.
4.2. Using SHOW CREATE TABLE
Another approach in MySQL for listing foreign keys is using the SHOW CREATE TABLE statement. In particular, it provides the complete CREATE TABLE syntax for a given table, including foreign key definitions.
For example, let’s list the foreign keys of the Registration table:
SHOW CREATE TABLE Registration;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Registration | CREATE TABLE `Registration` (
`id` int NOT NULL,
`semester` varchar(30) DEFAULT NULL,
`year` int DEFAULT NULL,
`reg_datetime` datetime DEFAULT NULL,
`course_id` varchar(10) DEFAULT NULL,
`student_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `registration_course_id_fkey` (`course_id`),
KEY `registration_student_id_fkey` (`student_id`),
CONSTRAINT `registration_course_id_fkey` FOREIGN KEY (`course_id`) REFERENCES `Course` (`id`),
CONSTRAINT `registration_student_id_fkey` FOREIGN KEY (`student_id`) REFERENCES `Student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)The output of this command includes the full table creation statement, which clearly lists all foreign keys defined on the Registration table.
5. Conclusion
In this article, we’ve explored various methods for listing foreign keys across SQL Server, PostgreSQL, and MySQL. Understanding these methods is essential for maintaining referential integrity and ensuring robust database design.
Additionally, by understanding the different queries for each database, we can efficiently manage and troubleshoot foreign key relationships across different database systems.
The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.