循环外键约束失败mysql [英] Circular foreign key constraint fail mysql
本文介绍了循环外键约束失败mysql的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我在InnoDB引擎上设计了一个数据库,但是我有3个无法删除的表,因为每个表都说外键约束失败"-这是否表示设计错误?
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?
请参见下面的结构:
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;
我怀疑问题出在default_identity_id ...我应该将其作为标志移至身份表吗?
I suspect the problem is with default_identity_id... shall I move it as a flag to identity table?
请告知!
推荐答案
由于ypercube没有创建我会回答的问题.
Since ypercube did not create an answer I will.
这回答了我的问题-它比仅将表指向彼此要好得多,而且灵活得多.谢谢.
This answers my question - it is far better and flexible design than just pointing tables to each other. Thanks.
这篇关于循环外键约束失败mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文