Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know you cannot drop a table that has active foreign key constraints.

So I was wondering is it safe to:

ALTER TABLE myTable NOCHECK CONSTRAINT all
DROP TABLE myTable

Rather than dropping the constraints first, table dropping:

ALTER TABLE myTable DROP Constraint FK_MyTable_AnotherTable
ALTER TABLE myTable DROP Constraint FK_MyTable_YetAnotherTable
DROP TABLE myTable 
share|improve this question
    
Did you try doing that? –  Aaron Bertrand Jan 28 '13 at 2:17
add comment

migrated from stackoverflow.com Jan 27 '13 at 13:50

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

SQL Server prevents you from dropping the table if a foreign key exists, nocheck or not:

create table t1
(id int not null, id2 int null)

create table t2
(id int primary key)

insert into t2 values (1)

insert into t1 values (1,1)

alter table t1 add constraint fk1 foreign key (id2) references t2(id)

alter table t1 nocheck constraint all

-- add a value to confirm nocheck working as expected
insert into t1 values (2,2)

-- lets clear out data from t1
truncate table t1

--alter table t1 drop constraint fk1

drop table t2

Gives you: Could not drop object 't2' because it is referenced by a FOREIGN KEY constraint.

So it's a moot point, really, you'll have to drop the FK first, either way.

share|improve this answer
    
Too bad SQL Server still doesn't support a cascading drop as e.g. PostgreSQL and Oracle. –  a_horse_with_no_name Jan 27 '13 at 13:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.