We also know some basics about the Primary Key.
The Primary Key constraint uniquely identifies each record in a database table. Primary Keys must contain unique values. By default, Primary key creates a clustered index on the column on which they are defined. A Primary key column cannot contain NULL values.
There are multiple ways of creating, altering and deleting Primary Key Constraint on a table:
How to create a PK with a constrain name while creating a table?
CREATE TABLE Students ( S_Id int NOT NULL, LName varchar(255) NOT NULL, FName varchar(255), Email_Id varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LName) )
Note that in the above code, we are explicitly specifying the name of the PK constraint.
How to add a PK constraint later (after the table was created)?
ALTER TABLE Students ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LName)
So, how do you remove it? Well, its simple:
ALTER TABLE Students DROP CONSTRAINT pk_StudentID
But what if you created a table with PK like this:
CREATE TABLE Students ( S_Id int NOT NULL PRIMARY KEY, LName varchar(255) NOT NULL, FName varchar(255), Email_Id varchar(255), Address varchar(255), City varchar(255) )
Note that PK constraint is created in the above code but you have not specified any constraint name. Now how do you drop it without the name?
Will this work?
ALTER TABLE Students DROP Primary Key
Well, not 🙁 – this works MySQL but not in SQL Server. And the error message is very interesting too 🙂
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'Primary'.
Well don’t worry; we have a solution for this:
As you have seen so far that if you know the name of Primary Key Constraint, you can easily be drop it.
But in the above case, SQL Server has automatically/implicitly assigned a name to this PK constraint. So the question is how to find that out? Well there are multiple ways again. Quickest could be:
See these few steps carefully:
- In your SQL Query Window, write the name of the Table in which you have declared Primary Key and Select it.
- Press ALT + F1 on your Keyboard. This is mainly being done to get all the details of the Students table. They are being displayed under the Results tab.
- So scrolling down, you will find the Constraint Name:
- Now after knowing the Constraint Name just use the simple query, which we saw earlier:
ALTER TABLE Students DROP CONSTRAINT PK__Students__A3DFF08D0AD2A005
Hope you enjoyed reading this. Do comment so that I can imporve in my next post.