问题描述
使用:SQL Server数据库:北风
Using: SQL ServerDatabase: Northwind
我想删除表主键,但不知道 PK 约束名称..
I'd like to drop a table primary key, without knowing the PK constraint name..
例如,使用Northwind Sample数据库中的Categories表,主键列是'CategoryId',主键名是'PK_Categories'
eg, using the Categories table in the Northwind Sample database, the primary key column is 'CategoryId', and the primary key name is '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.
我正在尝试:
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?
非常感谢,
鲍勃
推荐答案
为此您必须使用动态 SQL,因为 ALTER TABLE 不接受变量或子查询.
You'll have to use dynamic SQL for that, since ALTER TABLE does not accept variables or subqueries.
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
这篇关于SQL Server:删除表主键,不知道它的名字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!