CODEWITHSUNDEEP
sql-server
vatspoo
vatspoo

Reputation: 401

How to delete a primary key which doesn't have a name?

I am using SQL Server. Is there a way to drop the primary key which doesn't have a name?

Upvotes: 3

Views: 5300

Answers (3)

Johnny
Johnny

Reputation: 303

CREATE TABLE #Tempbox (Id int primary key identity(1,1), Name varchar(200) unique)

creating a table with column Id, and name, notice it doesn't have constraint name.

Well, if you don't set contraint name for column, sql server provides their own default unique constraint name to constraint like primary key, unique and so on.

1. Let's insert some value in our #Tempbox table

1. insert into #tempbox values ('Abc')
2. insert into #tempbox values ('Abc')

unique key violation error shown by sql server along with constraint name.

**Msg 2627, Level 14, State 1, Line 5
Violation of UNIQUE KEY constraint **'UQ__#Tempbox__737584F6A146D511'**. Cannot insert duplicate key in object 'dbo.#Tempbox'. The duplicate key value is (dsaf).
The statement has been terminated.**

you got the constraint name now.. which is 'UQ__#Tempbox__737584F6A146D511'

now let's drop the column with constraint. Remember you can't drop a column if it is using a constraint so in order to drop that column you will have to drop first constraint and after column.

ALTER TABLE #Tempbox drop constraint UQ__#Tempbox__737584F6A146D511; -- constraint is dropped

now drop the column 

alter table #tempbox drop column Name

Now use the same procedure for primary key column 

INSERT INTO #Tempbox (Id, Name) VALUES (1,'Abc')
INSERT INTO #Tempbox (Id, Name) VALUES (1,'Abc')

Error : 

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint **'PK__#Tempbox__3214EC07B3D09900'**. Cannot insert duplicate key in object 'dbo.#Tempbox'. The duplicate key value is (1).
The statement has been terminated.

drop constraint and drop column.

Another way to find the constraint name.. 

USE Adventure 

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='pERSON' AND TABLE_NAME='PERSON'

Upvotes: 0

Alex Aza
Alex Aza

Reputation: 78457

Primary key always has a name. If you didn't specify it in 'create table' or 'alter table' statement, the key name is auto-generated.
In the query bellow replace X with the name of your table and run the script.
It will drop the primary key.

declare @TableName sysname = 'X'

declare @PrimaryKeyName sysname = (
    select name
    from sys.key_constraints
    where type = 'PK' and parent_object_id = object_id(@TableName))

execute ('alter table ' + @TableName + ' drop constraint ' + @PrimaryKeyName)

It is always a good idea to specify explicit names for constraints, because if you run 'create/alter table' script on different databases, all of them will have different constraint name created.
Later, when you need to delete the constraints you need to run this workaround script, instead just running simple 'alter table drop constraint' statement.

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453288

It does have a name. Even if you don't name it explicitly SQL Server will auto create a name prefixed PK and based on the table name and the object_id of the constraint.

You can use the following query to see what it is.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo' AND TABLE_NAME='T'

The constraint name is required in the grammar for the DROP CONSTRAINT operation

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
   ....
    DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...n ]

Upvotes: 12

Related Questions