SQL Server – How to Drop a Table with Foreign Key Constraint

sql-server

I have the following:

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?

Best Answer

You must drop the constraint before you can drop the table. Otherwise its rule violation that could break the databases Referential Integrity.

How to get foreign key relationships see this old question. SQL DROP TABLE foreign key constraint

Related Question