Circular foreign key constraint fail mysql

Question

If I design a database on InnoDB engine and I have 3 tables which I can not delete because each says 'Foreign key constraint fail' - does it mean that design is wrong?

Please see the structure below:

CREATE TABLE IF NOT EXISTS `account` (
  `account_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `identity` (
  `identity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `account_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`identity_id`),
  KEY `fk_details1` (`user_id`),
  KEY `fk_account1` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(64) NOT NULL DEFAULT '',
  `password` varchar(32) NOT NULL DEFAULT '',
  `default_identity_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `login_UNIQUE` (`login`),
  KEY `fk_identity1` (`default_identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Constraints for table `identity`
--
ALTER TABLE `identity`
  ADD CONSTRAINT `fk_details1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_account1` FOREIGN KEY (`account_id`) REFERENCES `account` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `user`
--
ALTER TABLE `user`
  ADD CONSTRAINT `fk_identity1` FOREIGN KEY (`default_identity_id`) REFERENCES `identity` (`identity_id`) ON DELETE CASCADE ON UPDATE CASCADE;

I suspect the problem is with default_identity_id... shall I move it as a flag to identity table?

Please advise!

Circular references are hard to deal with. You can move the `default_identity_id` as a flag to `identity table` or you can add another `default_identity` table. See this similar problem: [MySQL Database design. Inserting rows in 1to1 tables](http://stackoverflow.com/questions/6691366/mysql-database-design-inserting-rows-in-1to1-tables/6692054#6692054) (Read those Parent-Child-BestChild as User-Identity-DefaultIdentity) — ypercubeᵀᴹ, Apr 09 '12 at 10:57
Very good. That's it. Please make it an answer so I can accept it :). — RandomWhiteTrash, Apr 09 '12 at 11:00
My MySQL 5.5.15.0 seems to be `DELETE`ing the circularly dependent data just fine. Could you please post the test data that reproduces the problem? — Branko Dimitrijevic, Apr 10 '12 at 06:32

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

Since ypercube did not create an answer I will.

MySQL Database design. Inserting rows in 1to1 tables

This answers my question - it is far better and flexible design than just pointing tables to each other. Thanks.

edited May 23 '17 at 12:20

Community

1
1

answered May 21 '12 at 08:34

RandomWhiteTrash

3,974
5
29
42

Circular foreign key constraint fail mysql

1 Answers1

Linked