在 SQL Server 数据库中使用脚本删除主键 [英] Drop primary key using script in SQL Server database

问题描述

我需要删除 SQL Server 数据库中表 Student 的主键.

I need to drop the primary key of a table Student in a SQL Server database.

我在表中编辑过，得到的脚本是

I have edited in the table and the script I got is

ALTER TABLE dbo.Student
    DROP CONSTRAINT PK__Student__9CC368536561EF8B

但是当我在 SQL Server 查询浏览器中运行此脚本以删除主键时

But when I run this script in SQL Server query browser to drop the primary key

显示消息

消息 3728，级别 16，状态 1，第 1 行
PK__Student__9CC368536561EF8B"不是约束条件.
消息 3727，级别 16，状态 0，第 1 行

我认为 PK__Student__9CC368536561EF8B 这将是随机生成的请帮助我使用 script 删除主键约束.

To my concern I think PK__Student__9CC368536561EF8B this will be generated randomly please help me to drop the primary key constraint using script.

提前致谢

推荐答案

可以在sys.key_constraints表中查找约束名称:

You can look up the constraint name in the sys.key_constraints table:

SELECT name
FROM   sys.key_constraints
WHERE  [type] = 'PK'
       AND [parent_object_id] = Object_id('dbo.Student');

如果你不在乎名字，只是想去掉，可以结合使用这个和动态sql:

If you don't care about the name, but simply want to drop it, you can use a combination of this and dynamic sql:

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.Student';

SELECT @sql = 'ALTER TABLE ' + @table 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

EXEC sp_executeSQL @sql;

此代码来自 Aaron Bertrand(来源).

This code is from Aaron Bertrand (source).

这篇关于在 SQL Server 数据库中使用脚本删除主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！