sql – Create a script to check if the table has a primary key

Question:

I need to create a script to check if a table has a primary key , if not, the primary key is added.

I found this way to make the script, but I don't understand where it takes the name of this table "INFORMATION_SCHEMA.TABLE_CONSTRAINTS" , and where "CONSTRAINT_TYPE" and "TABLE_SCHEMA" .

I'm starting to learn how to use SQL, if anyone can solve my doubt I appreciate it.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'Persons' 
AND TABLE_SCHEMA ='dbo')
BEGIN
   ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
END

Answer:

INFORMATION_SCHEMA is a special SQL Server schema used to obtain metadata from the databases that are on the server in question.

If you run:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

You will see that all database table constraints will be displayed in the result. What are you doing with this select:

SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'Persons' 
and COLUMN_NAME = 'P_Id' 
AND TABLE_SCHEMA ='dbo'

It's searching the dbo schema, Persons table and P_Id column if there is a primary key associated with it. If it doesn't exist, run this command:

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)