如果 SQL Server 中存在外键约束，如何删除表? [英] How can I drop a table if there is a foreign key constraint in SQL Server?

问题描述

我有以下几点:

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])
);

但是这是失败的消息说:

However this is failing with a message saying:

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.

在这些情况下如何删除表?

How can I drop a table in these circumstances?

推荐答案

必须先删除约束，然后才能删除表.否则违反规则.如何获得外键关系看到这个老问题.SQL DROP TABLE 外键约束

You must drop the constraint before you can drop the table.Other wise its rule violation. How to get foreign key relationships see this old question. SQL DROP TABLE foreign key constraint

这篇关于如果 SQL Server 中存在外键约束，如何删除表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！