SQL Query to Check or Find the Column Name Which Is Primary Key Column
Structured Query Language or SQL, is composed of commands that enable users to create database and table structures, perform various types of data manipulation and data administration and query the database to extract useful information. We will start with creating the database “geeksforgeeks” and then we will proceed with creating a table “interns” in that database. After this we will execute our query on the table.
Creating Database :
CREATE DATABASE geeksforgeeks;
To use above created database :
Create Table :
CREATE TABLE interns( id SERIAL PRIMARY KEY, name VARCHAR(30), gender VARCHAR(1), mobile BIGINT, email VARCHAR(35), city VARCHAR(25));
To see description of above created table :
Insert data into the table :
INSERT INTO interns(name, gender, mobile, email, city) VALUES ('Richa', 'F', '7999022923', 'firstname.lastname@example.org', 'Delhi'); INSERT INTO interns(name, gender, mobile, email, city) VALUES ('Shivam', 'M', '9999028833', 'email@example.com', 'Pune'); INSERT INTO interns(name, gender, mobile, email, city) VALUES ('Varnika', 'F', '7919490007', 'firstname.lastname@example.org', 'Mumbai'); INSERT INTO interns(name, gender, mobile, email, city) VALUES ('Abhishek', 'M', '7610573879', 'email@example.com', 'Bangalore');
View inserted data :
SELECT * FROM interns;
We are finished with creating database, creating table and inserting data into the table. Now, we have to find out the PRIMARY KEY of the created table and that PRIMARY KEY should be “id”.
To find PRIMARY KEY of any table :
SELECT K.COLUMN_NAME FROM
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
AND T.CONSTRAINT_TYPE=’PRIMARY KEY’ LIMIT 1;
SELECT K.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON K.CONSTRAINT_NAME=T.CONSTRAINT_NAME WHERE K.TABLE_NAME='interns' AND K.TABLE_SCHEMA='geeksforgeeks' AND T.CONSTRAINT_TYPE='PRIMARY KEY' LIMIT 1;
Here, we get COLUMN_NAME as “id” i.e. PRIMARY KEY of “interns” table.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS is a table which consists of information about all the tables created in any of the database till now. We require this table to validate the CONSTRAINT_TYPE. You can view information provided by the table by running below query.
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
Also, we have INFORMATION_SCHEMA.KEY_COLUMN_USAGE table which describes which key columns have constraints. We require this table to get the COLUMN_NAME. You can view information provided by the table by running below query.
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
So, we have joined these two tables TABLE_CONSTRAINTS (T) and KEY_COLUMN_USAGE (K) on CONSTRAINT_NAME. We have selected K.COLUMN_NAME of those records where K.TABLE_NAME = ‘interns’ and K.TABLE_SCHEMA = ‘geeksforgeeks’ and T.CONSTRAINT_TYPE = ‘PRIMARY KEY’.