Geek asks and answers

How to delete a primary key that does not have a name?

I am using SQL Server. Is there a way to remove a primary key that does not have a name?

+3
source share
3 answers

He has a name. Even if you do not specify it explicitly, SQL Server will automatically create a name with a prefix PKand based on the table name and object_idconstraint.

You can use the following query to find out what it is.

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

In grammar, an operation DROP CONSTRAINTrequires <restriction on restriction

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

. "create table" "alter table", .
X script.
.

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)

, create/alter table 'script , .
, , script, "alter table drop constraint".

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

, , .

, contraint , sql- , , ..

1. Let insert some value in our #Tempbox table

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

Unique key violation error displayed 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.**

now you got the name of the constraint 'UQ __ # Tempbox__737584F6A146D511'

now leave the constrained column. Remember that you cannot delete a column if it uses a constraint, therefore, to remove this column, you will need to delete the first constraint after the column as well.

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

now release the column

alter table #tempbox drop column Name

Now use the same procedure for the 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 limit and drop column.

Another way to find the name of the constraint ..

USE Adventure 

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

More articles:


All Articles