TheAuzzieJesus TheAuzzieJesus - 3 months ago 11
SQL Question

Unable to update a child row in my Database (MySQL)

Using the following SQL statement..

INSERT INTO `debates` (`id`, `unit_id`, `starter_pack_id`, `academic_id`, `title`, `date_due`, `date_created`, `date_modified`) VALUES
(1, 1, 1, 5, 'On Campus', '2016-12-25 00:00:00', now(), NULL),
(2, 1, 1, 5, 'Off Campus', '2016-12-25 00:00:00', now(), NULL);


Results in the following error being output..

#1452 - Cannot add or update a child row: a foreign key constraint fails (`my_database`.`debates`, CONSTRAINT `fk_debates_starter_pack_id` FOREIGN KEY (`starter_pack_id`) REFERENCES `debate_starter_packs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)



Why am I unable to update this table?


Please Note : Below are all the tables associated with this SQL statement. There are currently no tables contain any data in the database.

Debates:

CREATE TABLE `debates`(
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`unit_id` INT(11) UNSIGNED NOT NULL,
`starter_pack_id` TINYINT(3) UNSIGNED NOT NULL,
`academic_id` INT(11) UNSIGNED NOT NULL,
`title` VARCHAR(255) NOT NULL,
`date_due` DATETIME NOT NULL,
`date_created` DATETIME NOT NULL,
`date_modified` DATETIME DEFAULT NULL,
CONSTRAINT `pk_debates_id` PRIMARY KEY(`id`),
CONSTRAINT `fk_debates_starter_pack_id` FOREIGN KEY(`starter_pack_id`) REFERENCES `debate_starter_packs`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_debates_unit_id` FOREIGN KEY(`unit_id`) REFERENCES `units`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_debates_user_role_id` FOREIGN KEY(`academic_id`) REFERENCES `user_roles`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8;


Units (Unit_ID Reference):

CREATE TABLE `units` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`academic_id` int(11) UNSIGNED NOT NULL,
`unit_code` varchar(6) NOT NULL,
`title` varchar(100) DEFAULT NULL,
`date_added` datetime NOT NULL,
`date_modified` datetime DEFAULT NULL,
CONSTRAINT `pk_units_id` PRIMARY KEY(`id`),
CONSTRAINT `fk_units_academic_id` FOREIGN KEY (`academic_id`) REFERENCES `user_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Starter Packs (Starter_Pack_ID Reference):

CREATE TABLE `debate_starter_packs` (
`id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`academic_id` int(11) UNSIGNED NOT NULL,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,
`date_created` datetime NOT NULL,
`date_modified` datetime DEFAULT NULL,
CONSTRAINT `pk_debate_starter_packs_id` PRIMARY KEY(`id`),
CONSTRAINT `fk_debate_starter_packs_user_role_id` FOREIGN KEY (`academic_id`) REFERENCES `user_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Users (Academic_ID Reference):

CREATE TABLE `users` (
`id` int(11) UNSIGNED NOT NULL,
`username` varchar(100) DEFAULT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(255) NOT NULL,
`salt` varchar(255) DEFAULT NULL,
`organisation` varchar(100) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`date_created` datetime NOT NULL,
`date_last_login` datetime DEFAULT NULL,
`remember_code` varchar(40) DEFAULT NULL,
`active` tinyint(1) UNSIGNED NOT NULL,
CONSTRAINT `pk_users_id` PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer

Do you have the record id='1' in the table debate_starter_packs ?

The table debates have a foreign key reference id of table debate_starter_packs.

CONSTRAINT `fk_debates_starter_pack_id` FOREIGN KEY(`starter_pack_id`) REFERENCES `debate_starter_packs`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,

You should insert the record id='1' to the table debate_starter_packs first.