Drop primary key using script in SQL Server database

Question

I need to drop the primary key of a table Student in a SQL Server database.

I have edited in the table and the script I got is

ALTER TABLE dbo.Student
    DROP CONSTRAINT PK__Student__9CC368536561EF8B

But when I run this script in SQL Server query browser to drop the primary key

It shows the message

Msg 3728, Level 16, State 1, Line 1
'PK__Student__9CC368536561EF8B' is not a constraint.
Msg 3727, Level 16, State 0, Line 1

To my concern I think PK__Student__9CC368536561EF8B this will be generated randomly please help me to drop the primary key constraint using script.

Thanks in advance

are you sure, your Pk name is "PK__Student__9CC368536561EF8B" — Paz, Dec 19 '12 at 08:42
Check this:SELECT * FROM sysobjects WHERE name = 'PK__Student__9CC368536561EF8B' — Paz, Dec 19 '12 at 08:44
SELECT * FROM sysobjects WHERE name = 'PK__Student__9CC368536561EF8B' The error obtained is Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) — muthukumar, Dec 19 '12 at 09:18

score 107 · Accepted Answer · edited May 23 '17 at 12:03

107

You can look up the constraint name in the sys.key_constraints table:

SELECT name
FROM   sys.key_constraints
WHERE  [type] = 'PK'
       AND [parent_object_id] = Object_id('dbo.Student');

If you don't care about the name, but simply want to drop it, you can use a combination of this and dynamic sql:

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.Student';

SELECT @sql = 'ALTER TABLE ' + @table 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

EXEC sp_executeSQL @sql;

This code is from Aaron Bertrand (source).

edited May 23 '17 at 12:03

Community

1
1

answered Dec 19 '12 at 08:54

Bridge

29,818
9
60
82

Worked on SQL Server 2012. – nim_10 Mar 07 '16 at 08:56
2

This works as long as no other tables reference to table that primary key we want to delete. If you have tables referencing it (via a foreign key), then you must first drop all the referencing foreign keys also. – Krishna_K_Batham Dec 22 '16 at 08:42
Also relevant: https://learn.microsoft.com/en-us/sql/relational-databases/tables/delete-primary-keys?view=sql-server-2017 – Emmanuel Touzery Nov 05 '18 at 10:02
I keep getting an error: @sql must be a scalar variable. How do you fix that? – Bugz Jan 19 '22 at 15:10
Seems that ; ends the scope of the variables. So after removing the ";" the scalar variable error stopped – Bugz Jan 19 '22 at 15:27

score 19 · Answer 2 · edited Jun 04 '15 at 10:16

19

simply click

'Database'>tables>your table name>keys>copy the constraints like 'PK__TableName__30242045'

and run the below query is :

Query:alter Table 'TableName' drop constraint PK__TableName__30242045

edited Jun 04 '15 at 10:16

Artjom B.

61,146
24
125
222

answered Jun 04 '15 at 09:23

Harsh Bajaj

191
1
2

2

I slightly improved the formatting of your answer. Can you check whether the first actions string is correct? – Artjom B. Jun 04 '15 at 10:18
If you're navigating in SSMS, why not just right-click on the constraint you want to drop and click on the Delete option? – Task Oct 05 '18 at 17:33

score 5 · Answer 3 · edited Apr 06 '17 at 16:07

The answer I got is that variables and subqueries will not work and we have to user dynamic SQL script. The following works:

DECLARE @SQL VARCHAR(4000)
SET @SQL = 'ALTER TABLE dbo.Student DROP CONSTRAINT |ConstraintName| '

SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT   name
                                               FROM     sysobjects
                                               WHERE    xtype = 'PK'
                                                        AND parent_obj =        OBJECT_ID('Student')))

EXEC (@SQL)

Drop primary key using script in SQL Server database

3 Answers3

Linked

Related