4

I have a table having a foreign key to itself with on delete restrict. Now, if one record specifically refers to itself (i.e. is its own parent), how can I delete it without getting a foreign key error?

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uuid_was` varchar(36) NOT NULL,
  `uuid_is` varchar(36) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
ALTER TABLE `test`
  ADD UNIQUE KEY `uuid_was` (`uuid_was`);
ALTER TABLE `test` ADD FOREIGN KEY (`set`)
  REFERENCES `test`(`item`)
  ON DELETE RESTRICT ON UPDATE CASCADE;

INSERT INTO `test` (`item`, `set`) VALUES ('b', 'b');
DELETE FROM `test` WHERE `item`='b' and `set` = 'b'; // FOREIGN KEY FAIL

I understand there's SET FOREIGN_KEY_CHECKS=0 but can this be solved by improving my table/constraint definitions?

Derek Illchuk
  • 5,638
  • 1
  • 29
  • 29

0 Answers0