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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

So I was wondering is it safe to:


Rather than dropping the constraints first, table dropping:

ALTER TABLE myTable DROP Constraint FK_MyTable_AnotherTable
ALTER TABLE myTable DROP Constraint FK_MyTable_YetAnotherTable
share|improve this question

migrated from Jan 27 '13 at 13:50

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

Did you try doing that? – Aaron Bertrand Jan 28 '13 at 2:17
up vote 2 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

Your Answer


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