Enable and disable foreignkeys to insert data

Question

Enable and disable foreignkeys to insert data

komal145

SSCrazy Eights

Points: 9915
More actions
October 13, 2016 at 9:02 am

#331345

I am doing some tetsing and i wanted to insert data into the table which has foreign keys. How can i disable before insert and enable the constraint after insert?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

komal145 (10/13/2016)
I am doing some tetsing and i wanted to insert data into the table which has foreign keys. How can i disable before insert and enable the constraint after insert?

One way would be to something like:

--disable

ALTER TABLE YourTable

NOCHECK CONSTRAINT ALL

--Re-enable

ALTER TABLE YourTable

CHECK CONSTRAINT ALL

Sue

sunita2912 SSC Veteran Points: 295 More actions · Answer 2

--disable specific key

ALTER TABLE dbo.table1

WITH NOCHECK

NOCHECK CONSTRAINT [FK_table1_keyColumn]

GO

-- Enable specific key

ALTER TABLE dbo.table1

WITH CHECK CHECK CONSTRAINT [FK_table1_keyColumn]

GO

Joe Celko SSCertifiable Points: 5749 More actions · Answer 3

The short answer is you can do this with an alter check constraint. The right answer is that you are going to destroy your data integrity. Why would you want to test a schema without any data integrity?

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

komal145 SSCrazy Eights Points: 9915 More actions · Answer 4

This is development enviornment and since data is not current need to enforce the data for my testing. Anyways i inserted data without enabling or disabling , just by adding data to the foreignkey tables.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

komal145 (10/13/2016)
This is development enviornment and since data is not current need to enforce the data for my testing. Anyways i inserted data without enabling or disabling , just by adding data to the foreignkey tables.

Development environments represent what will eventually happen in prod. Disabling foreign keys even in Dev is a bad idea. What you ended up doing (correctly adding to the referenced tables) was the better way to do things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)