Retrieving Table Primary Key With SQL Queries

1. Overview

Primary keys are a fundamental concept in relational databases. In particular, they serve as a unique identifier for each record in a table. This ensures data integrity and enables efficient data retrieval.

Moreover, when managing or interacting with databases, it’s often necessary to identify these primary keys. This is especially useful when working with large and complex database schemas. Whether performing data migrations, setting up relationships between tables, or simply querying data, knowing how to retrieve primary key information is crucial.

In this tutorial, we’ll explore various ways to retrieve primary key details in PostgreSQL, MySQL, and SQL Server. Each database management system (DBMS) has tools and catalogs providing this information. We’ll use the simplified Baeldung University database for illustration.

2. In PostgreSQL

PostgreSQL, an advanced open-source relational database, provides multiple ways to retrieve information about primary keys. Additionally, it stores metadata about tables and their constraints in system catalogs. This makes it easy to query these catalogs to retrieve primary key information. Here, we explore three methods: using the pg_constraint system catalog, the information_schema views, and the pg_indexes catalog.

2.1. Using pg_constraint System Catalog

The pg_constraint catalog in PostgreSQL holds information about various constraints in the database, including primary keys.

For example, let’s retrieve the primary key constraints for the Student table by querying this catalog:

SELECT
    conname AS constraint_name, 
    conrelid::regclass AS table_name, 
    a.attname AS column_name
FROM 
    pg_constraint AS c
JOIN 
    pg_attribute AS a 
ON 
    a.attnum = ANY(c.conkey) 
    AND a.attrelid = c.conrelid
WHERE 
    c.contype = 'p' 
    AND c.conrelid = 'Student'::regclass;
+-----------------+------------+-------------+
| constraint_name | table_name | column_name |
|-----------------+------------+-------------|
| student_pkey    | student    | id          |
+-----------------+------------+-------------+
SELECT 1
Time: 0.009s

In this query, we join the pg_constraint catalog with pg_attribute to link the constraint to the specific column in the table. Then, the result shows the constraint name, table name, and column name associated with the primary key in the Student table.

2.2. Using information_schema

PostgreSQL also provides the information_schema views, which are part of the SQL standard and are supported by most relational databases. Additionally, these views offer a more database-agnostic way to retrieve metadata, including primary keys.

For example, we can retrieve primary key details from the Student table by querying these views:

SELECT
    kcu.column_name
FROM
    information_schema.table_constraints tc
JOIN
    information_schema.key_column_usage kcu
ON
    tc.constraint_name = kcu.constraint_name
WHERE
    tc.table_schema = 'public'
    AND tc.table_name = 'student'
    AND tc.constraint_type = 'PRIMARY KEY';
+-------------+
| column_name |
|-------------|
| id          |
+-------------+
SELECT 1
Time: 0.011s

In this query, we join the table_constraints and key_column_usage views from information_schema to filter out the primary key constraint associated with the Student table. The result lists the id column as the primary key for this table.

2.3. Using pg_indexes for Index Information

In PostgreSQL, primary keys are implemented as unique indexes, which are also stored in the pg_indexes catalog. Additionally, we can query the catalog to retrieve primary key information along with other index details.

For example, let’s retrieve the primary key details for the Student table by querying the pg_indexes catalog:

SELECT 
    indexname AS constraint_name,
    tablename AS table_name,
    indexdef AS definition
FROM 
    pg_indexes
WHERE 
    tablename = 'student'
    AND indexname = 'student_pkey';
+-----------------+------------+---------------------------------------------------------------------+
| constraint_name | table_name | definition                                                          |
|-----------------+------------+---------------------------------------------------------------------|
| student_pkey    | student    | CREATE UNIQUE INDEX student_pkey ON public.student USING btree (id) |
+-----------------+------------+---------------------------------------------------------------------+
SELECT 1
Time: 0.009s

This query returns the name of the primary key constraint and the index definition associated with it. In this result, the definition column shows the SQL command that PostgreSQL uses to create the primary key index on the id column of the Student table.

3. In MySQL

MySQL, a popular open-source relational database, offers several ways to retrieve primary key information for a table. Here, we explore two methods: using the SHOW KEYS command, and the SHOW INDEX command.

3.1. Using SHOW KEYS

MySQL provides a straightforward way to retrieve primary key information using the SHOW KEYS command. This command displays key information for a table, including primary keys.

For example, let’s retrieve the primary key for the Student table using the SHOW KEYS command:

SHOW KEYS FROM Student WHERE Key_name = 'PRIMARY';
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Student |          0 | PRIMARY  |            1 | id          | A         |          25 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.005 sec)

The result shows that the id column is the primary key for the Student table, and it’s indexed using the BTREE method. Additionally, the Non_unique column being 0 indicates that the index is unique, which is a requirement for primary keys.

3.2. Using SHOW INDEX

Another way to retrieve primary key information in MySQL is by using the SHOW INDEX command. This command provides similar information to SHOW KEYS but with a focus on the indexes within the table.

For example, let’s retrieve the primary key details for the Student table using the SHOW INDEX command:

SHOW INDEX FROM Student WHERE Key_name = 'PRIMARY';
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Student |          0 | PRIMARY  |            1 | id          | A         |          25 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.003 sec)

In this result, the SHOW INDEX command confirms that the id column serves as the primary key for the Student table. Similarly, it also provides details about the index type and column order within the primary key.

4. In SQL Server

SQL Server, a relational database management system developed by Microsoft, also provides several methods to retrieve primary key information for a table. Let’s explore three methods of retrieving a table’s primary key.

4.1. Using sys.key_constraints System Catalog

SQL Server stores metadata about constraints, including primary keys in system catalogs. The sys.key_constraints catalog holds information about key constraints, including primary keys. We can join this catalog with other system catalogs to retrieve detailed primary key information.

For example, let’s retrieve the primary key constraints for the Student table by querying this catalog:

SELECT
    kc.name AS constraint_name,
    t.name AS table_name,
    c.name AS column_name
FROM 
    sys.key_constraints AS kc
JOIN 
    sys.index_columns AS ic 
    ON kc.parent_object_id = ic.object_id 
    AND kc.unique_index_id = ic.index_id
JOIN 
    sys.columns AS c 
    ON ic.object_id = c.object_id 
    AND ic.column_id = c.column_id
JOIN 
    sys.tables AS t 
    ON kc.parent_object_id = t.object_id
WHERE 
    kc.type = 'PK' 
    AND t.name = 'Student';
+-----------------+------------+-------------+
| constraint_name | table_name | column_name |
|-----------------+------------+-------------|
| PK_Student      | Student    | id          |
+-----------------+------------+-------------+
(1 row affected)

In this query, we join the sys.key_constraints catalog with sys.index_columns, sys.columns, and sys.tables to link the constraint to the specific column in the table. Consequently, the result shows the constraint name, table name, and column name associated with the primary key in the Student table.

4.2. Using INFORMATION_SCHEMA

SQL Server supports the INFORMATION_SCHEMA views, which provide a standardized way to retrieve metadata about database objects, including primary keys. These views offer a more database-agnostic way to retrieve information.

For example, we can retrieve primary key details from the Student table by querying the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and INFORMATION_SCHEMA.TABLE_CONSTRAINTS views:

SELECT
    kcu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE 
    tc.TABLE_SCHEMA = 'dbo'
    AND tc.TABLE_NAME = 'Student'
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY';
+-------------+
| column_name |
|-------------|
| id          |
+-------------+
(1 row affected)

This query joins the TABLE_CONSTRAINTS and KEY_COLUMN_USAGE views from INFORMATION_SCHEMA to filter out the primary key constraint associated with the Student table.

4.3. Using sp_help Stored Procedure

SQL Server also provides a built-in stored procedure named sp_help, which returns information about a database object, including primary keys. This method is simple and quick, making it useful for exploratory queries.

For example, we can use sp_help to retrieve primary key details for the Student table:

EXEC sp_help 'Student';
index_name    index_description                                keys
------------- -----------------------------------------------  --------------
PK_Student    clustered, unique, primary key located on PRIMARY  id
(1 row affected)

The result includes a section that lists the primary key constraint for the Student table, including the column name and index type.

5. Conclusion

In this article, we’ve explored various methods to retrieve primary key information from tables across PostgreSQL, MySQL, and SQL Server databases. Understanding these methods is essential for database management tasks such as schema design, data migration, and query optimization.

By leveraging the tools and queries provided by each DBMS, we can efficiently access critical metadata and maintain the integrity of your database systems.