sql server - Removing a constraint in T-SQL by alter table command - problem

Question

I have got the following code in t-SQL:

alter table Persons
drop primary key;

And the message: Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'primary'.

I have checked different combinations of syntax and none have worked. What is wrong here?

This is how a table has been created - it is just beggining of studying, so very simple one with only two constraints.

create table Persons(
PersonID int not null primary key,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Answer 1

It's not DROP PRIMARY KEY it's DROP CONSTRAINT {Object Name}. For example:

CREATE TABLE dbo.YourTable (ID int NOT NULL);
GO
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (ID);
GO
ALTER TABLE dbo.YourTable DROP CONSTRAINT PK_YourTable;
GO
DROP TABLE dbo.YourTable;

This is why it's so important to explicitly name your objects, as shown above, as you now don't know what the name of the CONSTRAINT is. You could, however, get the name with the following:

SELECT kc.[name]
FROM sys.key_constraints kc
     JOIN sys.tables t ON kc.parent_object_id = t.object_id
     JOIN sys.schemas s ON t.schema_id = t.schema_id
WHERE s.[name] = N'dbo'
  AND t.[name] = N'YourTable'
  AND kc.[type] = 'PK';

If you really didn't want to find out the name and then write the statement, you could use a dynamic statement:

DECLARE @SQL nvarchar(MAX);
SET @SQL = (SELECT N'ALTER TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' DROP CONSTRAINT ' + QUOTENAME(kc.[name]) + N';'
            FROM sys.key_constraints kc
                 JOIN sys.tables t ON kc.parent_object_id = t.object_id
                 JOIN sys.schemas s ON t.schema_id = t.schema_id
            WHERE s.[name] = N'dbo'
              AND t.[name] = N'YourTable'
              AND kc.[type] = 'PK');
EXEC sys.sp_executesql @SQL;