我的mysql外键约束有什么问题？

Question

我正在开展一个个人项目，并且这是我一生中第一次尝试创建一个规范化的数据库（至少在第一个规范化级别）。下面是涉及我的问题的表格：

CREATE TABLE `reoccurrences` (
    `name` varchar(15) NOT NULL DEFAULT '', 
    `username` varchar(31) NOT NULL DEFAULT '', 
    `amount` float(7,2) NOT NULL DEFAULT '0.00', 
    `action` varchar(15) NOT NULL DEFAULT '', 
    `frequency` varchar(15) NOT NULL DEFAULT '', 
    PRIMARY KEY (`name`,`username`), 
    KEY `fk_reoccurrences_user` (`username`), 
    KEY `fk_reoccurrences_action` (`action`), 
    KEY `fk_reoccurrences_frequency` (`frequency`), 
    CONSTRAINT `fk_reoccurrences_action` FOREIGN KEY (`action`) REFERENCES `actions` (`name`), 
    CONSTRAINT `fk_reoccurrences_frequency` FOREIGN KEY (`frequency`) REFERENCES `frequencies` (`name`), 
    CONSTRAINT `fk_reoccurrences_user` FOREIGN KEY (`username`) REFERENCES `users` (`name`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

CREATE TABLE `days` (
    `reoccurrence` varchar(15) NOT NULL, 
    `username` varchar(31) NOT NULL DEFAULT '', 
    `day` tinyint(2) NOT NULL, 
    PRIMARY KEY (`reoccurrence`,`username`,`day`), 
    KEY `fk_days_reoccurrence2` (`username`), 
    CONSTRAINT `fk_days_reoccurrence` FOREIGN KEY (`reoccurrence`) REFERENCES `reoccurrences` (`name`), 
    CONSTRAINT `fk_days_reoccurrence2` FOREIGN KEY (`username`) REFERENCES `reoccurrences` (`username`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

注意，主要问题（我相信）是在这两个表之间。还有其他几个相关的表格，但如果我开始列出他们，这篇文章会变得很长。如果你想看到完整的数据结构，你可以在这里查看我的项目在github上：https://github.com/dallascaley/finance（我为我的github自述中的亵渎/缺乏常识而道歉，它不适合公众查看）

当我开始向这些表添加数据时会发生问题。这里是我有的数据：

reoccurrences: 

name, username, amount, action, frequency 
Pay , dallas , 2500 , credit, bi-weekly 
Rent, dallas , 1400 , debit , monthly 

days: 

reoccurrence, username, day 
Rent  , dallas , 1 

忽略我没有一天或几天上市支付的事实。由于某种原因，当表处于此状态时，我无法从重复发生表中删除Pay记录。当我运行此：

DELETE FROM reoccurrences WHERE `name` = 'Pay' AND `username` = 'dallas'; 

我得到以下错误：

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`days`, CONSTRAINT `fk_days_reoccurrence2` FOREIGN KEY (`username`) REFERENCES `reoccurrences` (`username`)) 

我怎么会改变我的表结构来解决这个问题？

Answer 1

你可能只需要修复表days的限制使用复合键从reoccurrences：

CREATE TABLE `days` (
    `reoccurrence` varchar(15) NOT NULL, 
    `username` varchar(31) NOT NULL DEFAULT '', 
    `day` tinyint(2) NOT NULL, 
    PRIMARY KEY (`reoccurrence`,`username`,`day`), 
    CONSTRAINT `fk_days_reoccurrence` FOREIGN KEY (`reoccurrence`,`username`) REFERENCES `reoccurrences` (`name`,`username`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

正如你可以看到我改变fk_days_reoccurrence到书写键和彻底消失fk_days_reoccurrence2，无论是键和外键引用。