Topic: 请教SQL Server的Primary Key问题 @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / 请教SQL Server的Primary Key问题Primary key was created by below statement:
ALTER TABLE myTable ADD PRIMARY KEY (col ASC)
Q: How to drop this primary key using T-SQL? I have tried "ALTER TABLE myTable DROP PRIMARY KEY" but it did not work. -sonican(田园); 2007-7-10 {215} (#3796113@0)

谢谢。我的问题是：PKName是随机生成的，但我不想在T-SQL中hardcode这个PKName，咋办？ -sonican(田园); 2007-7-11 (#3798053@0)

Try following code:DECLARE @PK_NAME VARCHAR(50)
SELECT @PK_NAME = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME ='YourTableName' AND CONSTRAINT_NAME LIKE 'PK_%'

Then you can drop it. -deep_blue(BLUE); 2007-7-11 {203} (#3798327@0)

曾试过ALTER TABLE pub_User_Groups DROP CONSTRAINT @constraintName，但不行： Incorrect syntax near '@constraintName' -sonican(田园); 2007-7-11 (#3798972@0)

Using dynamic SQLDeclare @sql VARCHAR(200)
SELECT @sql = ‘ALTER TABLE pub_User_Groups DROP CONSTRAINT ‘ + @constraintName
EXEC (@sql)

HTH -deep_blue(BLUE); 2007-7-12 {127} (#3800086@0)

PK 名在 exec sp_MStablekeys TableName,null,14 中可以取到,希望对你有帮助 -jimi.cui(jimi); 2007-7-11 (#3799392@0)
use this SQL -jimi.cui(jimi); 2007-7-13 {618} (#3804340@0)

why-- alter table nums drop constraint @PK_NAME doesn't work? -hieveryone(:-)); 2007-7-16 (#3811023@0)
Why does the following not work?alter table nums add primary key (n)

DECLARE @PK_NAME VARCHAR(50)
SELECT @PK_NAME = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME ='nums' AND CONSTRAINT_NAME LIKE 'PK_%'

alter table nums drop constraint @PK_NAME

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@PK_NAME'. -hieveryone(:-)); 2007-7-16 {343} (#3811085@0)

In command, alter table nums drop constraint PK_Name, PK_Name actually is an object rather than a varchar variable. Therefore, when PK_Name is a variable, you need use dynamic SQL: Exec (‘alter table nums drop constraint ‘ + @PK_NAME). HTH -deep_blue(BLUE); 2007-7-17 (#3811506@0)

@Florida