如果我在 InnoDB 引擎上设计一个数据库,我有 3 个表,我无法删除,因为每个表都说"外键约束失败" - 这是否意味着设计是错误的?
请参阅以下结构:
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上......我应该将其作为标志移动到标识表吗?
请指教!
由于 ypercube 没有创建答案,我会的。
MySQL 数据库设计。在 1 对 1 表中插入行
这回答了我的问题 - 它比仅仅将桌子指向彼此更好和灵活得多。谢谢。