Foreign Key Failure

Question

Ok, so I've been trying to figure out why I keep getting this specific error. MySql keeps giving me error code 1452. Cannot add or update a child row. My tables are as such.

CREATE  TABLE IF NOT EXISTS `ecommerce`.`departments` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `name` VARCHAR(100) NOT NULL ,
 PRIMARY KEY (`id`) )
ENGINE = MyISAM;

INSERT INTO `ecommerce`.`departments`
VALUES (1, 'Development'), (2, 'Marketing'),
       (3, 'Sales'), (4, 'Customer Service');

CREATE  TABLE IF NOT EXISTS `ecommerce`.`department_roles` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `name` VARCHAR(100) NOT NULL ,
 `map` VARCHAR(255) NOT NULL ,
 `parent_id` INT NOT NULL ,
 PRIMARY KEY (`id`) )
ENGINE = MyISAM;

INSERT INTO `ecommerce`.`department_roles`
VALUES (1, 'Admin', '/admin', 0), (2, 'Create', '/admin', 1),
       (3, 'Update', '/admin', 1), (4, 'Delete', '/admin', 1);

CREATE  TABLE IF NOT EXISTS `ecommerce`.`department_roles_map` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `department_roles_id` INT NOT NULL ,
 `departments_id` INT NOT NULL ,
 PRIMARY KEY (`id`) ,
 INDEX `fk_drm_departments` (`departments_id` ASC) ,
 INDEX `fk_drm_department_roles` (`department_roles_id` ASC) ,
 CONSTRAINT `fk_drm_departments`
  FOREIGN KEY (`departments_id` )
  REFERENCES `ecommerce`.`departments` (`id` )
  ON DELETE CASCADE
  ON UPDATE NO ACTION,
 CONSTRAINT `fk_drm_department_roles`
  FOREIGN KEY (`department_roles_id` )
  REFERENCES `ecommerce`.`department_roles` (`id` )
  ON DELETE CASCADE
  ON UPDATE NO ACTION)
ENGINE = InnoDB;

Now, when I try to select from departments and department_roles, I show data.

SELECT * FROM department_roles;
+----+--------+--------+-----------+
| id | name   | map    | parent_id |
+----+--------+--------+-----------+
|  1 | Admin  | /admin |         0 |
|  2 | Create | /admin |         1 |
|  3 | Update | /admin |         1 |
|  4 | Delete | /admin |         1 |
+----+--------+--------+-----------+
4 rows in set (0.00 sec)

SELECT * FROM departments;
+----+--------+--------+-----------+
| id | name   | map    | parent_id |
+----+--------+--------+-----------+
|  1 | Admin  | /admin |         0 |
|  2 | Create | /admin |         1 |
|  3 | Update | /admin |         1 |
|  4 | Delete | /admin |         1 |
+----+--------+--------+-----------+
4 rows in set (0.00 sec)

But, when I try to insert into department_roles_map, I get this.

INSERT INTO department_roles_map(department_roles_id, departments_id) VALUES (1, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ecommerce`.`department_roles_map`, CONSTRAINT `fk_drm_departments` FOREIGN KEY (`departments_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION)

Any help would be much appreciated!

score 1 · Accepted Answer · answered Mar 20 '12 at 16:04

1

Firstly, I'm impressed that you managed to create an InnoDB table that has FK references to two MyISAM tables!

Try creating all three table with InnoDB engine and trying again....

answered Mar 20 '12 at 16:04

Tom Mac

9,693
3
25
35

score 0 · Answer 2 · answered Mar 20 '12 at 16:07

Both the parent and the child tables need to use the InnoDB storage engine, but you're using MyISAM for the parent tables.

My guess is that there is already a table named department_roles_map, so when you runCREATE TABLE IF NOT EXISTS it's failing because the table already exists, and ignoring the error. Then when you try to insert data into the other department_roles_map, it fails with the FK error.

But that's just a guess.

I agree with Tom Mac, try creating all 3 tables using InnoDB, but you should also confirm that no other tables with those names already exists.

Foreign Key Failure

2 Answers2