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
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
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
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)