sql-server - 如果 SQL Server 中存在外键约束，如何删除表？

Question

我有以下内容:

DROP TABLE [dbo].[ExtraUserInformation];
DROP TABLE [dbo].[UserProfile];
DROP TABLE [dbo].[webpages_Membership];
DROP TABLE [dbo].[webpages_OAuthMembership];
DROP TABLE [dbo].[webpages_Roles];
DROP TABLE [dbo].[webpages_UsersInRoles];

CREATE TABLE [dbo].[ExtraUserInformation] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [UserId]   INT            NOT NULL,
    [FullName] NVARCHAR (MAX) NULL,
    [Link]     NVARCHAR (MAX) NULL,
    [Verified] BIT            NULL,
    CONSTRAINT [PK_dbo.ExtraUserInformation] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[webpages_UsersInRoles] (
    [UserId] INT NOT NULL,
    [RoleId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [fk_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[UserProfile] ([UserId]),
    CONSTRAINT [fk_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[webpages_Roles] ([RoleId])
);

然而，这失败了，并显示一条消息:

Msg 3726, Level 16, State 1, Line 6
Could not drop object 'dbo.UserProfile' because it is referenced by a FOREIGN KEY constraint.
Msg 3726, Level 16, State 1, Line 9
Could not drop object 'dbo.webpages_Roles' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 27
There is already an object named 'UserProfile' in the database.
Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

在这种情况下如何删除表格？

Answer 1

您必须先删除约束，然后才能删除表。否则，违反规则可能会破坏数据库引用完整性。

如何获取外键关系请参阅这个老问题。 SQL DROP TABLE foreign key constraint