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

Have you actually searched for the correct syntax? It's clearly documented in [bol](https://learn.microsoft.com/en-us/sql/relational-databases/tables/delete-primary-keys?view=sql-server-ver15) — HoneyBadger, Apr 06 '21 at 11:41
Does this answer your question? [Finding a Primary Key Constraint on the fly in SQL Server 2005](https://stackoverflow.com/questions/6683778/finding-a-primary-key-constraint-on-the-fly-in-sql-server-2005) — Zhorov, Apr 06 '21 at 11:44
@HoneyBadger - it doesn't work in my case - the same message. — CompactSpaces, Apr 06 '21 at 11:57
Beware - you need to drop all the related foreign keys first. This smells like a big [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) as well as a task you are not well prepared to handle. — SMor, Apr 06 '21 at 11:57
@CompactSpaces if you get the same error you haven't read the instructions carefully enough, because it doesn't use `primary` — HoneyBadger, Apr 06 '21 at 12:43

Thom A · Answer 1 · 2021-04-06T11:50:29.743

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;

I'm sorry, for sure it is a piece of cake and maybe the answer is in the other posts here and I am just blind and not the sharpest tool in the shed yet, but PK_YourTable; - what is that? When I type PK_Persons it doesn't work and my constraint does not have any special name. — CompactSpaces, Apr 06 '21 at 12:18
`PK_YourTable` is the name of the primary key in the table `dbo.YourTable`; you see me define this in the SQL statement above it, @CompactSpaces . If you read my answer, you see me talk about that due to the way you haven't explicitly defined your Primary Key's name, you don't know what its name is. Read the answer in full, it explains everything; including how to get the name of your Primary Key. — Thom A, Apr 06 '21 at 12:20
"my constraint does not have any special name" yes it does, you just don't know what it is yet. — HoneyBadger, Apr 06 '21 at 13:21

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

1 Answers1

Linked