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

Asked

Viewed 4,180 times

3

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

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

I’m starting to learn how to handle SQL, if anyone can remedy my question I thank. 

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
sql sql-server

  • the script may even work and you can use it as a didactic form, but there is no real application for it. Primary keys are not created in the second moment, at runtime, but in development time.

  • I don’t know what to make of that question. I even found the doubt interesting, as a technical curiosity, but it gives me a certain cold in the belly to imagine where it would be used in practice.

  • Bacco, probably this table is only used to load a drop, which made table not bothered to put Primary key thinking it would only be used in the drop, the more the system grows and there is a need to create the Primary key to link with another table.

2 answers

0


INFORMATION_SCHEMA is a special SQL Server schema, used to retrieve metadata from the databases on the server in question.

If you run:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

You’ll see that all the 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'

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

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)

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 command "INFORMATION_SCHEMA" can have the information of all the metadata of the current database, it is using together with another command (INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.ROTINE_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 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 is that it does not have the primary key then the alter table will be executed, if it finds nothing will be done.

I hope to have helped and clarified some doubts of the commands.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.