59

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. How can I drop the primary key constraint using script?

Improve this question
3
  • are you sure, your Pk name is "PK__Student__9CC368536561EF8B" Commented Dec 19, 2012 at 8:42
  • Check this:SELECT * FROM sysobjects WHERE name = 'PK__Student__9CC368536561EF8B' Commented Dec 19, 2012 at 8: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.) Commented Dec 19, 2012 at 9:18

3 Answers 3

Reset to default
111

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Worked on SQL Server 2012.
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.
Also relevant: learn.microsoft.com/en-us/sql/relational-databases/tables/…
I keep getting an error: @sql must be a scalar variable. How do you fix that?
Seems that ; ends the scope of the variables. So after removing the ";" the scalar variable error stopped
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
Improve this answer

2 Comments

I slightly improved the formatting of your answer. Can you check whether the first actions string is correct?
If you're navigating in SSMS, why not just right-click on the constraint you want to drop and click on the Delete option?
5

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.