通过 alter table 命令删除 T-SQL 中的约束 - 问题

2021-09-10 00:00:00 constraints drop tsql sql-server alter-table

我在 t-SQL 中有以下代码:

I have got the following code in t-SQL:

alter table Persons
drop primary key;

还有消息:消息 156，级别 15，状态 1，第 10 行关键字primary"附近的语法不正确.

我检查了不同的语法组合，但都没有奏效.这里有什么问题?

I have checked different combinations of syntax and none have worked. What is wrong here?

表格就是这样创建的 - 这只是学习的开始，非常简单，只有两个约束.

This is how a table has been created - it is just beggining of studying, so very simple one with only two constraints.

create table Persons(
PersonID int not null primary key,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

推荐答案

不是DROP PRIMARY KEY，而是DROP CONSTRAINT {Object Name}.例如:

CREATE TABLE dbo.YourTable (ID int NOT NULL);
GO
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (ID);
GO
ALTER TABLE dbo.YourTable DROP CONSTRAINT PK_YourTable;
GO
DROP TABLE dbo.YourTable;

这就是为什么显式命名您的对象如此重要的原因，如上所示，因为您现在不知道 CONSTRAINT 的名称是什么.但是，您可以通过以下方式获取名称:

This is why it's so important to explicitly name your objects, as shown above, as you now don't know what the name of the CONSTRAINT is. You could, however, get the name with the following:

SELECT kc.[name]
FROM sys.key_constraints kc
     JOIN sys.tables t ON kc.parent_object_id = t.object_id
     JOIN sys.schemas s ON t.schema_id = t.schema_id
WHERE s.[name] = N'dbo'
  AND t.[name] = N'YourTable'
  AND kc.[type] = 'PK';

如果你真的不想找出名字然后写语句，你可以使用动态语句:

If you really didn't want to find out the name and then write the statement, you could use a dynamic statement:

DECLARE @SQL nvarchar(MAX);
SET @SQL = (SELECT N'ALTER TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' DROP CONSTRAINT ' + QUOTENAME(kc.[name]) + N';'
            FROM sys.key_constraints kc
                 JOIN sys.tables t ON kc.parent_object_id = t.object_id
                 JOIN sys.schemas s ON t.schema_id = t.schema_id
            WHERE s.[name] = N'dbo'
              AND t.[name] = N'YourTable'
              AND kc.[type] = 'PK');
EXEC sys.sp_executesql @SQL;

相关文章