問題描述

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

I have got the following code in t-SQL:

alter table Persons
drop primary key;

還有消息:消息 156，級別 15，狀態(tài) 1，第 10 行關(guān)鍵字primary"附近的語法不正確.

我檢查了不同的語法組合，但都沒有奏效.這里有什么問題?

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

表格就是這樣創(chuàng)建的 - 這只是學(xué)習(xí)的開始，非常簡單，只有兩個約束.

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;

這就是為什么顯式命名您的對象如此重要的原因，如上所示，因為您現(xiàn)在不知道 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';

如果你真的不想找出名字然后寫語句，你可以使用動態(tài)語句:

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;

這篇關(guān)于通過 alter table 命令刪除 T-SQL 中的約束 - 問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！