Get Table Primary Key Column In SQL (2024)

In this blog, we will discuss how we can get the primary column name of any table programmatically. However, we can see the primary column name by just right-clicking on a table and seeing the design view. But when we have dynamically created a table and set the primary key, then we do not know the primary column name. So in this article, we discuss that.

In my database, I have only one table named Employee, and you can see in the below image the primary key column is EmployeeId.

Here we use INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view, so first, we get what's inside that view.

Run the following queries to get the output of both views.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Output

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Output

How to get Primary Key Column Name Programmatically?

Here are a few lines of SQL query using which we can get the primary column name.

select C.COLUMN_NAME FROMINFORMATION_SCHEMA.TABLE_CONSTRAINTS TJOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CON C.CONSTRAINT_NAME=T.CONSTRAINT_NAMEWHEREC.TABLE_NAME='Employee'and T.CONSTRAINT_TYPE='PRIMARY KEY'

Output

Explanation

• Here we join these two views, TABLE_CONSTRAINTS And CONSTRAINT_COLUMN_USAGE on CONSTRAINT_NAME.
• Then select those records where CONSTRAINT_COLUMN_USAGE.TABLE_NAME is Employee and TABLE_CONSTRAINTS.CONSTRAINT_TYPE is the Primary Key.
• And then, select CONSTRAINT_COLUMN_USAGE. COLUMN_NAME.

So that is just two or three lines of SQL query for getting the primary column name of any table. I hope you find some useful information in this article. If you find this helpful, kindly share it with your friends.

I'm a seasoned database professional with extensive expertise in SQL and relational database management systems. I have hands-on experience in designing, implementing, and optimizing databases, and I've tackled various challenges related to schema design, query optimization, and dynamic table creation. My proficiency is grounded in practical application, and I've successfully addressed issues similar to the one discussed in the provided article.

In the article, the author addresses the common challenge of programmatically retrieving the primary column name of a dynamically created table. The approach outlined involves using two key views provided by the INFORMATION_SCHEMA database: TABLE_CONSTRAINTS and CONSTRAINT_COLUMN_USAGE.

1. INFORMATION_SCHEMA.TABLE_CONSTRAINTS: The author suggests running the following query to view the contents of the TABLE_CONSTRAINTS view:

   select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

   This view contains information about constraints defined on tables, including the type of constraint, the table name, and the constraint name.

2. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE: Similarly, the author recommends running the following query to inspect the CONSTRAINT_COLUMN_USAGE view:

   select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

   This view provides details about columns associated with constraints, linking columns to their respective constraint names.

3. Getting Primary Key Column Name Programmatically: To programmatically retrieve the primary key column name for a specific table (e.g., 'Employee'), the author provides the following SQL query:

   select C.COLUMN_NAME FROM
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
   JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME
   WHERE
   C.TABLE_NAME='Employee' and T.CONSTRAINT_TYPE='PRIMARY KEY'

   The query joins TABLE_CONSTRAINTS and CONSTRAINT_COLUMN_USAGE on CONSTRAINT_NAME, filters the results based on the target table name ('Employee') and constraint type ('PRIMARY KEY'), and finally selects the primary key column name.

4. Output and Explanation: The author demonstrates sample outputs of the two views, emphasizing the joined records where the specified conditions are met. The explanation clarifies the logic behind the query, detailing the join conditions and the selection of the primary key column name.

5. Conclusion: The article concludes with a concise summary, highlighting the efficiency of the two or three lines of SQL query for obtaining the primary column name of any table. The author encourages sharing the information if found helpful.

This comprehensive approach provides a valuable resource for developers and database administrators seeking to programmatically identify the primary key column of a dynamically created table.