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

Navigation
3

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

I found this form to do the script, I did not understand where it takes the name of this table "INFORMATION_SCHEMA.TABLE_CONSTRAINTS" , and in where "CONSTRAINT_TYPE" and "TABLE_SCHEMA" .

I'm starting to learn how to tweak SQL, if anyone can heal my doubt thank you. 

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
    
asked by anonymous 26.11.2015 / 18:06

2 answers

0

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 constraints of database tables 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'

Look in the schema dbo , table Persons , and column P_Id if there is a primary key associated with it. If it does not exist, run this command: 

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
    
26.11.2015 / 20:09
0

Krispim,

use the following select: 

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

The "INFORMATION_SCHEMA" command is able to have all the metadata information of the current database, it is used together with another command (INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.ROUTINE_COLUMNS, INFORMATION_SCHEMA.TABLES source link )

In the case using the command "KEY_COLUMN_USAGE" (select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE) will be listed all the fields that are primary key and in which tables are.

By doing the select I suggested you can find out if the "Persons" table has the key in the "P_Id" field, if it does not return anything in the select it does not have the primary key then the alter table will be executed, nothing will be done.

I hope I have helped and clarified some of the commands' questions.

    
26.11.2015 / 19:48
Error connecting to MySQL PDO Return E-mail from friends Facebook friend_list