Hello Folks,
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.
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
You can also use the script as follows :
01 DECLARE @Constraint_Name SYSNAME, @Table_Name SYSNAME, @Constraint_Type NVARCHAR(200), @Database_Name SYSNAME
02 DECLARE @SQLStmt NVARCHAR(4000), @Params NVARCHAR(4000)
03 SELECT @Table_Name = ‘Test_a’ –‘Table_Name’
04 , @Constraint_Type = ‘PRIMARY KEY’
05 SELECT @Constraint_Name = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
06 WHERE TABLE_NAME = @Table_Name
07 AND CONSTRAINT_TYPE = @Constraint_Type
08
09 SELECT @SQLStmt = N’
10 RAISERROR(”PRIMARY KEY CONSTRAINT [%s] is being dropped from Table [%s] on Database [%s]”,0,1,@Constraint_Name,@Table_Name,@Database_Name) WITH NOWAIT
11 ALTER TABLE ‘+@Table_Name+’
12 DROP CONSTRAINT ‘+@Constraint_Name+’
13 ‘
14 , @Params = N’@Table_Name SYSNAME, @Constraint_Name SYSNAME, @Database_Name SYSNAME’
15 , @Database_Name = DB_NAME()
16 EXEC sp_executeSQL @stmt = @SQLStmt, @params = @Params
17 , @Table_Name = @Table_Name
18 , @Constraint_Name = @Constraint_Name
19 , @Database_Name = @Database_Name
The above can be even wrapped in APIs (SQL Stored Procedures, JOB SQL steps etc)
Hi Piyush
With Help of this below script we can drop primary key constraint without Constraint name Please try…………………………….
DECLARE @SQL VARCHAR(2000)
SET @SQL=’ALTER TABLE students DROP CONSTRAINT’ + ‘ ‘ +
(select name from sysobjects where xtype = ‘PK’ and
parent_obj = object_id(‘students’))
exec (@SQL)
Thanks
Manoj (mkumar_dba)
Informative post, thanx.
do we always need to hard code the constraint name?
Hi SriramSu and Manoj, i am glad to see your response. You both have given a very nice queries, and thanx for the great learning.
Hi Prashant, its really not necessary to give the constraint name specially for Primary Key . But if want to give explicitly as a constraint you might have to add a constraint name.
Piyush,
It really Good…..
Thanks Joydeep!!!
that’s fine article about droping primary key in the table,but arises qusetion when the table linked to another table(s) with primary key?i meannig parent and child tables,
or
…also when i created trigger for preventing to drop data inthe child table, and tested it, first fired the error statment:: The DELETE statement conflicted with the REFERENCE constraint “FK_makavshirebeli_avtori”. The conflict occurred in database “my db”, table “dbo.makavshirebeli”, column ‘avtoriID’.,but not trigger’s statment
how to set more than one primary key in a table using SQL SERVER 2008 r2?..
In pl/sql…Composite key is referred..
and thanks for your document
nice explanation.
how to add and drop a not null constraint?
@mahalakshmi thanks 🙂
For adding a NULL constraint:
1 ALTER TABLE
2 ALTER COLUMN NOT NULL
For removing a NULL constraint:
1 ALTER TABLE
2 ALTER COLUMN NULL
@kavi… welcome 🙂
You can only have one primary key, but you can have multiple columns in your primary key, which is called as Composite Primary Key.
Its really Good…
Perfect! Thanks 🙂
Thank you very much sir . Before reading this article I have spent many hours but no use. your post is greatly appreciated …….thank you for your time………