Is there a simpler and faster way of dropping a table that has foreign key references to other tables?

Question

I have a new table that has foreign key references to a number of existing tables. When I create the new table for the first time, I created the foreign keys in the following way:

ALTER TABLE [dbo].[NewTable] WITH CHECK ADD FOREIGN KEY([SectionDatabaseID])
REFERENCES [dbo].[Section] ([SectionDatabaseID])

ALTER TABLE [dbo].[NewTable] WITH CHECK ADD FOREIGN KEY([TermDatabaseID])
REFERENCES [dbo].[Term] ([TermDatabaseID])

The above way will generate names for the foreign key constraints using it's own automated naming convention.

However, I want to drop the new table because I need to do some major modifications by creating it from scratch.

Obviously, if I just execute a drop statement, the SQL Server database will complain stating that the new table has foreign key references to other tables.

I've heard of running the following script to figure out what foreign keys exist so that we can drop them:

use perls;
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME +
'] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' and TABLE_NAME = 'NewTable'

The above will give results that basically show alter statements which I need to run.

I need something more automated. Why do I have to drop each foreign key constraint separately, and then only be able to drop the table?

I want to drop the table in a simpler way. It's got to be easier than what I need to do above.

you could create a diagram, add all your tables, and drop the FKs through that UI. — Beth, Oct 03 '12 at 19:16
You should be able to drop tables with FOREIGN KEYs referencing other tables without trouble. The problem comes when trying to drop a table that has FOREIGN KEYs referencing *it*. In those cases, you must find and drop the FOREIGN KEYs first. — Larry Lustig, Oct 03 '12 at 19:16
As I understand you want a `Drop table cascade` equivalent in tsql? See http://stackoverflow.com/questions/4858488/sql-server-drop-table-cascade-equivalent — Draco Ater, Oct 03 '12 at 19:17
I would explicitly name the foreign key constraint according to a naming convention (up to you what to use in detail) - that way you should be able to *know* what the FK constraints are called, so you can more easily drop them. — marc_s, Oct 03 '12 at 19:32

score 4 · Answer 1 · answered Oct 03 '12 at 19:12

Relational databases are designed to enforce database integrity. Your statements are potentially destroying constraints that were carefully added to protect the data quality. Automating the dropping of integrity constraints from a production database could be a disaster for a company. So when the SQL language was developed, making it easy to drop tables that are referenced by other tables was not high on the priority of important tasks. With a database engine for enterprise data, the product is designed more for DBAs than for casual users.

What is the reason for wanting to drop multiple tables that are being referenced in foreign keys? Can you be sure that you are not going to be destroying somebody else's queries?

Assuming that you are working on a non critical system, your approach of writing a script to write a drop script is about the best solution that I know of.

During development it is extremely common to write your table creation scripts such that they can be run again after modifying the schema. In this case, you need to drop all tables and constraints your script creates before re-creating them. — Crake, Mar 12 '13 at 16:36

mellodev · Accepted Answer · 2012-10-03T19:27:32.663

Yes you must drop the FKs first. I've ran into this before and have written just the script you need to automate the task. Replace DBName and TABLENAME below.

Warning to all: This script will drop a specific table (or group of tables) as well as all FK constraints! Use with caution, and always take a db backup before doing a major operation like this.

    use DBName
    /* 
    SCRIPT TO DROP TABLES WITH FK'S, INDEXES 
    https://mellodev.snipt.net/drop-tables-with-fk-contraints/
    */

    set nocount on

declare @tables table (tablename varchar(255));
insert into @tables
select name from sys.tables where name in 
('TABLENAME')

-- Iterate tables, drop FK constraints and tables
declare @tablename varchar(255);
declare cTable cursor for
select tablename from @tables
open cTable
fetch next from cTable into @tableName
while @@FETCH_STATUS=0
begin
    -- Identify any FK constraints
    declare @fkCount int;
    SELECT @fkCount = COUNT(*)
    FROM sys.foreign_keys
    WHERE referenced_object_id = object_id(@tablename)

    -- Drop any FK constraints from the table
    if (@fkCount>0) begin
        declare @dropFkSql nvarchar(max);set @dropFkSql='';
        declare @fkName nvarchar(max);
        declare cDropFk cursor for  
            SELECT 'ALTER TABLE [' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']',name
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id(@tablename)
        open cDropFk
        fetch next from cDropFk into @dropfksql,@fkName
        while @@FETCH_STATUS=0
        begin       
            exec sp_executesql @dropFkSql;

            select 'Dropped FK Constraint: ' + @fkName
            fetch next from cDropFk into @dropfksql,@fkName
        end
        close cDropFk
        deallocate cDropFk          
    end

    -- Drop the table
    declare @dropTableSql nvarchar(max);
    set @dropTableSql='DROP TABLE [' + @tablename + ']';
    exec sp_executesql @dropTableSql;

    select 'Dropped table: ' + @tablename

    fetch next from cTable into @tableName
end
close cTable
deallocate cTable

score 1 · Answer 3 · answered Oct 03 '12 at 19:34

If you are using MSSQL then you can use this script that i wrote to purge the whole DB from data without dropping the tables themselves.

use [MyDataBase]
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

select tbl.* into dbo.DeleteQueries from (SELECT 'DELETE ' + name + ';' as query
FROM sys.tables
WHERE name <> 'DeleteQueries')  as tbl

Declare @query nvarchar(100)

While (Select Count(*) From dbo.DeleteQueries Where query <> '') > 0
Begin
    Select Top 1 @query = query From dbo.DeleteQueries Where query <> ''

    exec(@query);

    Update dbo.DeleteQueries Set query = '' Where query = @query 

End

DROP TABLE dbo.DeleteQueries

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

you can add tables that you don't want to purge in the where clause of the "select into" query. Hope this helps

Is there a simpler and faster way of dropping a table that has foreign key references to other tables?

3 Answers3