How can I drop a table if there is a foreign key constraint in SQL Server?

Question

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?

Check out this question: http://stackoverflow.com/questions/1776079/sql-drop-table-foreign-key-constraint This is a common SQL Server task — MPH, Feb 27 '13 at 08:26
Possible duplicate of [SQL DROP TABLE foreign key constraint](http://stackoverflow.com/questions/1776079/sql-drop-table-foreign-key-constraint) — matthieusb, Apr 14 '17 at 14:43
In case someone is using MS SQL Server Management Studio: https://stackoverflow.com/a/71969142/7389293 — carloswm85, Apr 22 '22 at 12:58

score 22 · Accepted Answer · edited Oct 18 '21 at 02:47

22

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

edited Oct 18 '21 at 02:47

Jeremy Thompson

61,933
36
195
321

answered Feb 27 '13 at 08:30

4b0

21,981
30
95
142

score 11 · Answer 2 · edited Dec 11 '16 at 00:21

11

1-firstly, drop the foreign key constraint after that drop the tables.

2-you can drop all foreign key via executing the following query:

DECLARE @SQL varchar(4000)=''
SELECT @SQL = 
@SQL + 'ALTER TABLE ' + s.name+'.'+t.name + ' DROP CONSTRAINT [' + RTRIM(f.name) +'];' + CHAR(13)
FROM sys.Tables t
INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id
INNER JOIN sys.schemas     s ON s.schema_id = f.schema_id

--EXEC (@SQL)

PRINT @SQL

if you execute the printed results @SQL, the foreign keys will be dropped.

edited Dec 11 '16 at 00:21

mike nelson

21,218
14
66
75

answered Mar 18 '14 at 14:33

murat

139
1
5

This generates **all** DROP CONSTRAINT statements, not just the ones "we" care about. – RonJohn Oct 16 '20 at 18:01

score 6 · Answer 3 · edited Apr 25 '14 at 19:53

6

The Best Answer to dropping the table containing foreign constraints is :

Step 1 : Drop the Primary key of the table.
Step 2 : Now it will prompt whether to delete all the foreign references or not.
Step 3 : Delete the table.

edited Apr 25 '14 at 19:53

Tay2510

5,748
7
39
58

answered Apr 25 '14 at 19:32

Nitin Kumar

69
1
1

thanks, this worked for me. all i wanted was to run script to update users table in the DB, and to test, I wanted to just copy the user table from live to dev copy... not have to copy the entire 5gb DB. – Shaakir Dec 10 '21 at 07:33

score 4 · Answer 4 · answered Jan 05 '15 at 19:00

4

To drop a table if there is a foreign key constraint in MySQL Server?

Run the sql query:

SET FOREIGN_KEY_CHECKS = 0; DROP TABLE table_name

Hope it helps!

answered Jan 05 '15 at 19:00

MageDevBrossard

308
2
5

2

The question is about Microsoft SQL Server not MySQL – Martin Smith Jan 05 '15 at 19:40
1

Although, this question was about SQL Server, I wanted to do the same thing in MySQL and this answer was what helped me. – Adim Victor Jun 25 '20 at 11:38

score 4 · Answer 5 · answered Jul 05 '18 at 05:42

    --Find and drop the constraints

    DECLARE @dynamicSQL VARCHAR(MAX)
    DECLARE MY_CURSOR CURSOR 

    LOCAL STATIC READ_ONLY FORWARD_ONLY 
    FOR
        SELECT dynamicSQL = 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'
        FROM sys.foreign_keys
        WHERE object_name(referenced_object_id)  in ('table1', 'table2', 'table3')
    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @dynamicSQL
    WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT @dynamicSQL
        EXEC (@dynamicSQL)

        FETCH NEXT FROM MY_CURSOR INTO @dynamicSQL
    END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR


    -- Drop tables
    DROP 'table1'
    DROP 'table2'
    DROP 'table3'

score 3 · Answer 6 · answered Oct 29 '15 at 23:18

You have to drop the constraint before drop your table.

You can use those queries to find all FKs in your table and find the FKs in the tables in which your table is used.

Declare @SchemaName VarChar(200) = 'Your Schema name'
Declare @TableName VarChar(200) = 'Your Table Name'

-- Find FK in This table.
SELECT 
    ' IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = 
OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + 
OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(FK.parent_object_id) +
    '.[' + OBJECT_NAME(FK.parent_object_id) + 
    '] DROP CONSTRAINT ' + FK.name
    , S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O 
  ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S 
  ON (O.schema_id = S.schema_id)  
WHERE 
      O.name = @TableName
      And S.name = @SchemaName


-- Find the FKs in the tables in which this table is used
  SELECT 
    ' IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =   
      OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
  + ''') AND parent_object_id = OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + 
 OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

    ' ALTER TABLE ' +  OBJECT_SCHEMA_NAME(FK.parent_object_id) +
    '.[' + OBJECT_NAME(FK.parent_object_id) + 
    '] DROP CONSTRAINT ' + FK.name
    , S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O 
  ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S 
  ON (O.schema_id = S.schema_id)  
WHERE 
      O.name = @TableName
      And S.name = @SchemaName

This is insane. I have a table which has a foreign key constraint on itself (its own column references another table). SQL Server really should have the smarts to just drop that contraint before it drops the table when I issue a command to drop the table. Unfathomable that it can't handle that. — youcantryreachingme, Dec 17 '21 at 02:31

score 1 · Answer 7 · edited May 23 '17 at 12:02

BhupeshC and murat , this is what I was looking for. However @SQL varchar(4000) wasn't big enough. So, small change

DECLARE @cmd varchar(4000)

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 

select 'ALTER TABLE ['+s.name+'].['+t.name+'] DROP CONSTRAINT [' + RTRIM(f.name) +'];' FROM sys.Tables t INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id INNER JOIN sys.schemas s ON s.schema_id = f.schema_id

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
    -- EXEC (@cmd)
    PRINT @cmd
    FETCH NEXT FROM MY_CURSOR INTO @cmd
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

GO

score 0 · Answer 8 · answered Nov 16 '21 at 17:43

Re removing constraints and dropping the tables, this script can get all constraints for a selected list of tables and then drop the constraints and then delete the tables.

DECLARE @id INT
DECLARE @strSQL NVARCHAR(max)
DECLARE @deleteCurser CURSOR
DECLARE @deleteTableCurser CURSOR



declare @SelectedTables table ([name] sysname, [object_id] int)
Insert into @SelectedTables ([name], [object_id])
SELECT t.name, t.object_id 
FROM sys.Tables t
where t.name in
-- Change Name of tables here
('table1','table2','table100')



SET @deleteTableCurser = CURSOR FOR
SELECT strSQL = 
'ALTER TABLE ' + s.name+'.'+t.name + ' DROP CONSTRAINT [' + RTRIM(f.name) +'];'
FROM @SelectedTables t
INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id
INNER JOIN sys.schemas     s ON s.schema_id = f.schema_id


OPEN @deleteTableCurser
FETCH Next 
FROM @deleteTableCurser INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN
    print(@strSQL)
    EXEC (@strSQL)
    FETCH NEXT
    FROM @deleteTableCurser INTO @strSQL
END

CLOSE @deleteTableCurser
DEALLOCATE @deleteTableCurser


SET @deleteCurser = CURSOR FOR
SELECT strSQL = 'DROP TABLE ['+ t.name+ '];'
FROM @SelectedTables t

OPEN @deleteCurser
FETCH Next 
FROM @deleteCurser INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN
    print(@strSQL)
    EXEC (@strSQL)
    FETCH NEXT
    FROM @deleteCurser INTO @strSQL
END


CLOSE @deleteCurser
DEALLOCATE @deleteCurser

score -3 · Answer 9 · edited Aug 13 '16 at 02:41

-3

Type this .... SET foreign_key_checks = 0;
delete your table then type SET foreign_key_checks = 1;

MySQL – Temporarily disable Foreign Key Checks or Constraints

edited Aug 13 '16 at 02:41

zarak

2,933
3
23
29

answered Aug 13 '16 at 00:50

pissa

1

How can I drop a table if there is a foreign key constraint in SQL Server?

9 Answers9

Linked