What is wrong with my mysql foreign key constraints?

Question

I am working on a personal project and for the first time ever in my life i'm actually trying to make a database that is normalized (at least to the first thee levels of normalization). Here are the table's involved in my problem:

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;

Note, the main problem (I believe) is between these two tables. There are several other related tables but if i start to list them this post would get very long. If you want to see the full data structure you can view my project on github here: https://github.com/dallascaley/finance (and i appologize for the profanity/lack of common sense in my github readme, it wasn't intended for public viewing)

The problem happens when i start to add data to these tables. Here is the data i have:

reoccurrences:

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

days:

reoccurrence, username, day
Rent        , dallas  , 1

Ignore the fact that I don't have a day, or several days listed for Pay. For some reason when the table is in this state, I can not delete the Pay record from the reoccurrence table. When i run this:

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

I get the following error:

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

How could I change my table structure to fix this problem?

Answer 1

You probably just need to fix your constraints in table days to use the composite key from 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;

As you can see I changed fk_days_reoccurrence to a compose key and completely dropped fk_days_reoccurrence2, both the key and foreign key references.