Menu
I have many tables with relationship constraints. Tables contain full of dummy data, i would like to truncate/empty the data from the tables while keeping the structure.
Everytime i want to empty a table, i get foreign key constraint errors. What can i do to disable the constraint temporarily and then set enable it back once dummy data are deleted.
I tried this solution, but didn't work! Once i disable, then try to truncate, i get fk constraint error...
//disable
SET FOREIGN_KEY_CHECKS = 0;
//enable
SET FOREIGN_KEY_CHECKS = 1;
I'm using MySQL.
Any idea?
748
Use SQL Server Management StudioIn 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. Select Close.
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);
You can use the ALTER TABLE statement to enable, disable, modify, or drop a constraint. When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise.
You have to remove the FK if you want to use truncate, as truncate not a logged operation.
Can use delete with FK in place but delete is a logged operation and takes longer.
157
Start at the end (tables that are only FK) and not referenced BY any other tales. Those you can truncate. Then work your way up and delete. If you truncate / delete in the proper order then you will not violate the contraints. Or you could drop all the constaints. Truncate the tables. Then add the contraints back in.
27
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
