Reputation: 32758
How can I drop a table if there is a foreign key constraint in 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?
Upvotes: 30
Views: 175894
Answers (9)
Reputation: 2098
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
Upvotes: 0
Reputation: 22323
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
Upvotes: 23
Reputation: 171
--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'
Upvotes: 4
Reputation: 149
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.
Upvotes: 11
Reputation: 1
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
Upvotes: -3
Reputation: 12555
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
Upvotes: 3
Reputation: 11
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
Upvotes: 1
Reputation: 308
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!
Upvotes: 4
Reputation: 69
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.
Upvotes: 6
Related Questions
- SQL DROP TABLE foreign key constraint
- How do I drop a foreign key constraint only if it exists in sql server?
- Cannot drop a table because of foreign keys
- Can I drop a foreign key without using constraint?
- How to DROP a table which has constraints, without dropping child tables?
- Drop table with foreign key
- SQL Server drop foreign key constraint
- How to Drop a table with it's all Foreign Keys Constraint?
- How can I only drop a table and a constraint if they exist?
- How to drop tables with no constraint checking