CODEWITHSUNDEEP
sqlsql-serverconstraints
PavanKumar GVVS
PavanKumar GVVS

Reputation: 1045

Unable to drop Primary Key from SQL Server table

I have primary key for a table. I checked constraints by using below query.

 SELECT name
 FROM sys.key_constraints
 WHERE [type] = 'PK'
   AND [parent_object_id] = Object_id('<TableName>');

Output of above query: PK_dbo.TableName (only 1 constraint of entire table)

I am trying to drop above constraint by using below query.

 ALTER TABLE TableName DROP CONSTRAINT PK_dbo.TableName;

I tried as like below but same error.

 ALTER TABLE dbo.TableName DROP CONSTRAINT PK_dbo.TableName;

Query execution failed with error message :

Failed to execute query. Error: Incorrect syntax near '.'

What is wrong in above query?

Upvotes: 1

Views: 3062

Answers (2)

Bibin Mathew
Bibin Mathew

Reputation: 465

It is because of the . in your primary key name. It can be avoided by using [ ] like this:

ALTER TABLE dbo.TableName DROP CONSTRAINT [PK_dbo.TableName];

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Make sure you have the dot . in your constraint name.

If it is there, then type the constraint name inside square brackets, otherwise make sure you are giving the correct name

ALTER TABLE dbo.TableName DROP CONSTRAINT [PK_dbo.TableName];

Note: It is not recommended to use characters like dot, blank space etc in object names. Instead, use underscores like PK_dbo_TableName

Upvotes: 2

Related Questions