SQL Server：删除表主键，不知道它的名字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1587812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: drop table primary key, without knowing its name
提问by Stuart Ainsworth
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 Sample数据库中的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
鲍勃
回答by Stuart Ainsworth
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
回答by Przemyslaw Remin
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
