SQL Server：删除表主键，不知道其名称

HI,

HI，

Using: SQL Server Database: Northwind

使用：SQL Server数据库：Northwind

I'd like to drop a table primary key, without knowing the PK constraint name..

我想删除一个表主键，而不知道PK约束名称..

eg, using the Categories table in the Northwind Sample database, the primary key column is 'CategoryId', and the primary key name is 'PK_Categories'

例如，使用Northwind示例数据库中的Categories表，主键列为'CategoryId'，主键名为'PK_Categories'

I can drop the primary key while knowing the primary key name:

我知道主键名称时可以删除主键：

ALTER TABLE categories DROP CONSTRAINT PK_Categories;

And I can also get the primary key name for the table by table name:

我还可以通过表名获取表的主键名称：

select name from sysobjects where xtype = 'PK' and parent_obj = object_id('categories')

However, I cannot put them together to delete a table's primary key, without first knowing the primary key name.

但是，我不能将它们放在一起删除表的主键，而不必先知道主键名。

I am trying:

我在尝试：

ALTER TABLE categories DROP CONSTRAINT


(select name from sysobjects where xtype = 'PK' and parent_obj = object_id('categories') ) 

Can anyone show me where I am going wrong?

有谁能告诉我哪里出错了？

Many thanks,

非常感谢，

Bob

短发

2 个解决方案

#1

You'll have to use dynamic SQL for that, since ALTER TABLE does not accept variables or subqueries.

您必须使用动态SQL，因为ALTER TABLE不接受变量或子查询。

CREATE TABLE PKTest ( ID INT PRIMARY KEY ) ;

DECLARE @SQL VARCHAR(4000)
SET @SQL = 'ALTER TABLE PKTEST DROP CONSTRAINT |ConstraintName| '

SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT   name
                                               FROM     sysobjects
                                               WHERE    xtype = 'PK'
                                                        AND parent_obj = OBJECT_ID('PKTest')
                                             ))

EXEC (@SQL)

DROP TABLE PKTest

#2

Adding to Stuart Ainsworth answer, I do not know if PK name has to be unique across different schemas (if so, that answer is ok). Anyway I would choose different sub query for PK name, allowing explicitly to define schema:

添加到Stuart Ainsworth回答，我不知道PK名称是否必须在不同的模式中是唯一的（如果是这样，答案是可以的）。无论如何，我会为PK名称选择不同的子查询，允许显式定义模式：

declare @PrimaryKeyName sysname = 
    (select CONSTRAINT_NAME 
     from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     where CONSTRAINT_TYPE = 'PRIMARY KEY' and TABLE_SCHEMA='dbo' and TABLE_NAME = 'PKTest'
    )

IF @PrimaryKeyName is not null
begin
    declare @SQL_PK NVARCHAR(MAX) = 'alter table dbo.PKTest drop constraint ' + @PrimaryKeyName
    print (@SQL_PK)
    EXEC sp_executesql @SQL_PK;
end