how to drop a primary key in sql server?

Question

how to drop a primary key with t-sql(i mean script) like alter table tablename modify primary key..! how to write a script to drop a primary key? thanks in advance!!!!

Pரதீப் · Accepted Answer · 2016-10-15T13:45:46.697

9

To Drop any constraint this is the method

ALTER TABLE Yourtable -- Table Name
DROP CONSTRAINT PK_primarykey_name -- Primary Key name

If you don't know the primary key constraint name then run the below script to know

SELECT CONSTRAINT_NAME  
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  TABLE_NAME = 'yourtable'  -- Table Name
       AND TABLE_SCHEMA = 'dbo'  -- change it if table is in some other schema 
       AND CONSTRAINT_TYPE = 'PRIMARY KEY'

edited Oct 15 '16 at 13:45

answered Oct 15 '16 at 13:39

Pரதீப்

91,748
19
131
172

i got an error msg – snl Oct 15 '16 at 13:52
like this-- Msg 3728, Level 16, State 1, Line 1 'id' is not a constraint. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors. – snl Oct 15 '16 at 13:53
1

@SunilThoutam - So it is not your constraint name. Run my second query and add proper constraint name – Pரதீப் Oct 15 '16 at 13:54
thanq its done..! – snl Oct 15 '16 at 14:03
create TABLE Persons1 ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID1 PRIMARY KEY (P_Id,LastName,firstname) ) – snl Oct 15 '16 at 14:04
@SunilThoutam `pk_PersonID1` is the primary key constraint name – Pரதீப் Oct 15 '16 at 14:05
when i execute.. in design it is showing two primary keys..what it exactly means thanks in advance – snl Oct 15 '16 at 14:05
primary key constraint name is "PK__tblperso__3213E83F03317E3D" – snl Oct 15 '16 at 14:06
can you tell me above table what is primary key in that which has two fields? – snl Oct 15 '16 at 14:09
@SunilThoutam - A table can have only one primary key. It is not possible did you add this filter `CONSTRAINT_TYPE = 'PRIMARY KEY'` – Pரதீப் Oct 15 '16 at 14:12
actually it is created that why i am confused.! – snl Oct 15 '16 at 14:15
@SunilThoutam - ask a new question with relevant informations added in question – Pரதீப் Oct 15 '16 at 14:19

how to drop a primary key in sql server?

1 Answers1