Disable Foreign key constraint on a table?

Question

Can i temporarily disable a foreign key constraint. How do i do this?

Check also this related question: [Can foreign key constraints be temporarily disabled using TSQL?](http://stackoverflow.com/questions/159038) — kristof, Apr 08 '09 at 07:58

score 25 · Accepted Answer · answered Apr 08 '09 at 01:59

25

To temporarily disable a constraint (foreign keys are constraints):

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

To re-enable a constraint

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

answered Apr 08 '09 at 01:59

Chris Shaffer

31,567
5
48
61

be warned this will fail if your data is not referntially in tact. – ojblass Apr 08 '09 at 02:04

score 1 · Answer 2 · answered Apr 08 '09 at 12:53

1

Incidentally, this is why you need "Alter table" permissions when you BCP or Bulk Insert data into a table. Using the default configuration, check constraints and foreign keys are not checked.

answered Apr 08 '09 at 12:53

Andy Jones

1,397
9
15

Disable Foreign key constraint on a table?

2 Answers2