Menu
I want to batch insert a large amount of generated data which has a circular dependency (a column in each table is foreign key constrained to the other table). To get around this, I want to just turn off the foreign key constraints, insert the data, and then turn the constraints back on.
Googling around, I found a bunch of solutions, but none of them worked. Right now I have:
ALTER TABLE TableName NOCHECK CONSTRAINT ALL
The command runs and doesn't produce any errors, but when I attempt to clear the table in preparation for inserting the data, I get the following error:
System.Data:0:in `OnError': The DELETE statement conflicted with the REFERENCE constraint "FK_1_2_ConstraintName". The conflict occurred in database "DatabaseName", table "dbo.SomeOtherTable", column 'PrimaryKey'.\r\nThe statement has been terminated.\r\nChecking identity information: current identity value '0', current column value '0'.\r\nDBCC execution completed. If DBCC printed error messages, contact your system administrator. (System::Data::SqlClient::SqlException)
My current theory is that this is caused by a foreign key constraint on the other table which depends on the table being changed.
There are two solutions I can come up with to this problem:
Go through all the tables with dependencies on the table I'm inserting into and disable their foreign key constraints. This seems unnecessarily complicated.
Disable foreign key constraints on all of the tables in the database.
Either solution would work, but I'm not sure where to start on either solution. Any ideas?
296
To disable a foreign key constraint for INSERT and UPDATE statements. In Object Explorer, expand the table with the constraint and then expand the Keys folder. Right-click the constraint and select Modify. In the grid under Table Designer, select Enforce Foreign Key Constraint and select No from the drop-down menu.
You can disable foreign key check in MySQL by setting the system variable foreign_key_checks to 0. However, please note, after you enable foreign key checks, MySQL will not re-validate your existing data that you added after disabling foreign key check. It will only check any new additions/updates to your database.
There are multiple ways to disable constraints in Oracle. constraint_name; Another way to enable and disable constraints in Oracle would be to either use a plsql block or write a script. Execute Immediate 'alter table '||:tab_name||' disable constraint '||tabCons(numCount);
Setting foreign_key_checks to 0It affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables.
This is what I used for this kind work.
--Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- INSERT DATA HERE
--Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
