CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keysforeign-key-relationship
Zetaphor
Zetaphor

Reputation: 504

Foreign Key Constraint Fails - Probably misunderstanding something about my relationships

I'm having a little trouble with some MySQL relationships. I think I'm missing something obvious in my structure. Here's my SQL:

DROP TABLE IF EXISTS `parentlist_comments`;
CREATE TABLE `parentlist_comments` (
  `id` char(36) NOT NULL,
  `parentlist_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `comment` char(50) NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT '0',
  `submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_parentlist_comments_parentlist` (`parentlist_id`),
  KEY `fk_parentlist_comment_user` (`user_id`),
  CONSTRAINT `fk_parentlist_comments_parentlist` FOREIGN KEY (`parentlist_id`) REFERENCES `parentlists` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_parentlist_comment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `parentlist_submissions`;
CREATE TABLE `parentlist_submissions` (
  `id` char(36) NOT NULL,
  `parentlist_id` char(36) NOT NULL,
  `type_id` char(36) NOT NULL,
  `name` char(25) NOT NULL,
  `user_id` char(36) NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT '0',
  `submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `votes` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_parentlist_submissions_user` (`user_id`),
  KEY `fk_parentlist_submissions_list` (`parentlist_id`),
  KEY `fk_parentlist_submissions_type` (`type_id`),
  CONSTRAINT `fk_parentlist_submissions_list` FOREIGN KEY (`parentlist_id`) REFERENCES `parentlists` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_parentlist_submissions_type` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_parentlist_submissions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `parentlists`;
CREATE TABLE `parentlists` (
  `id` char(36) NOT NULL,
  `name` char(20) NOT NULL,
  `description` char(50) NOT NULL,
  `user_id` char(36) NOT NULL,
  `max_comments` int(3) NOT NULL DEFAULT '0',
  `max_submissions` int(3) NOT NULL DEFAULT '10',
  `max_votes` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_list_user` (`user_id`),
  CONSTRAINT `fk_list_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

DROP TABLE IF EXISTS `submissions`;
CREATE TABLE `submissions` (
  `id` char(36) NOT NULL,
  `type_id` char(36) NOT NULL,
  `name` char(30) NOT NULL,
  `description` char(50) NOT NULL,
  `embed` char(200) DEFAULT NULL,
  `user_id` char(36) NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT '0',
  `submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `votes` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_submission_user` (`user_id`),
  KEY `fk_submission_type` (`type_id`),
  CONSTRAINT `fk_submission_type` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_submission_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `types`;
CREATE TABLE `types` (
  `id` char(36) NOT NULL,
  `name` char(20) NOT NULL,
  `description` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `name` char(20) NOT NULL,
  `password` char(20) NOT NULL,
  `email` char(50) NOT NULL,
  PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I created an column called submission_id in parentlist_submissions. I'm trying to create a foreign key relationship between parentlist_submissions.submission_id and submissions.id, when I attempt to do this I get the error: Foriegn key constraint fails. For whatever reason my query browser won't let me copy this.

Any help here is greatly appreciated!

Upvotes: 0

Views: 1144

Answers (2)

Ted Hopp
Ted Hopp

Reputation: 234795

That error is usually caused by the tables already being populated with data that violate the constraint. (Note that nulls may be a problem if you've just added the column.)

I'm guessing, because I don't see that you've posted the statement where you create the submission_index column or where you create the foreign key constraint.

Upvotes: 2

Phil Sandler
Phil Sandler

Reputation: 28016

You seem to be missing the "parentlist_submissions.submission_id" column.

Upvotes: 0

Related Questions