Menu
I have a self-referential MySQL table with a recursive parent_id:
CREATE TABLE `recursive` ( `id` int(11) NOT NULL auto_increment, `parent_id` int(11) default NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `data_categorysource_parent_id` (`parent_id`), CONSTRAINT `parent_id_refs_id_627b4293` FOREIGN KEY (`parent_id`) REFERENCES `data_categorysource` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 During testing, I want to empty it but TRUNCATE fails:
TRUNCATE `recursive` /* SQL Error: Cannot delete or update a parent row: a foreign key constraint fails... I currently have to manually delete all records, starting at the bottom of the tree working upwards. This gets onerous even with small trees.
Is there an easy way around this? I can't DROP the table and re-create it easily as other tables reference it (I have already truncated those so there should be no data integrity issues there).
715
To delete a foreign key constraintIn Object Explorer, expand the table with the constraint and then expand Keys. Right-click the constraint and then click Delete. In the Delete Object dialog box, click OK.
To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];
Table student_exam has foreign key student_id referencing student_id in student_details table. Here, ON DELETE CASCADE is added because when any row is deleted in one table the same gets deleted in the foreign referenced tables that are referencing the primary key in that table.
Dropping Foreign Key Constraints You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
Why not:
UPDATE 'recursive' SET 'parent_id' = NULL WHERE 'parent_id' IS NOT NULL; DELETE FROM 'recursive'; ?
165
If you just want to empty the whole thing for testing purposes use:
SET FOREIGN_KEY_CHECKS = 0; // Execute Query SET FOREIGN_KEY_CHECKS = 1; This totally bypasses any foreign key checks.
42
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
