MySQL foreign key error while inserting into tables (error 1452), already created FKs

Question

i have an issue with my db where i can not insert data into it, it keeps showing me this error : Mysql: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails. I am not trying to create new foreign keys, i am just trying to insert data into tables I have 4 tables, here is the creation code below:

CREATE TABLE IF NOT EXISTS `projectx`.`restaurants` (
  `restaurant_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `restaurant_name` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`restaurant_id`)  COMMENT '',
  UNIQUE INDEX `restaurant_id_UNIQUE` (`restaurant_id` ASC)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projectx`.`table_area`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projectx`.`table_area` (
  `area_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `width` INT NOT NULL COMMENT '',
  `height` INT NOT NULL COMMENT '',
  PRIMARY KEY (`area_id`)  COMMENT '',
  UNIQUE INDEX `area_id_UNIQUE` (`area_id` ASC)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projectx`.`table_position`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projectx`.`table_position` (
  `position_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `pos_x` INT NOT NULL COMMENT '',
  `pos_y` INT NOT NULL COMMENT '',
  PRIMARY KEY (`position_id`)  COMMENT '',
  UNIQUE INDEX `position_id_UNIQUE` (`position_id` ASC)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `projectx`.`restaurant_tables`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `projectx`.`restaurant_tables` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `table_id` INT NOT NULL COMMENT '',
  `restaurant_id` INT NOT NULL COMMENT '',
  `table_kind` VARCHAR(10) NULL COMMENT '',
  `number_of_seats` INT NOT NULL COMMENT '',
  `is_taken` TINYINT(1) NOT NULL COMMENT '',
  `table_area_id` INT NOT NULL COMMENT '',
  `table_position_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`id`, `restaurant_id`, `table_area_id`, `table_position_id`)  COMMENT '',
  UNIQUE INDEX `id_UNIQUE` (`id` ASC)  COMMENT '',
  INDEX `fk_restaurant_tables_restaurants_idx` (`restaurant_id` ASC)  COMMENT '',
  INDEX `fk_restaurant_tables_table_area1_idx` (`table_area_id` ASC)  COMMENT '',
  INDEX `fk_restaurant_tables_table_position1_idx` (`table_position_id` ASC)  COMMENT '',
  CONSTRAINT `fk_restaurant_tables_restaurants`
    FOREIGN KEY (`restaurant_id`)
    REFERENCES `projectx`.`restaurants` (`restaurant_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_restaurant_tables_table_area1`
    FOREIGN KEY (`table_area_id`)
    REFERENCES `projectx`.`table_area` (`area_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_restaurant_tables_table_position1`
    FOREIGN KEY (`table_position_id`)
    REFERENCES `projectx`.`table_position` (`position_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Insert code :

insert into restaurant_tables(id,table_id,restaurant_id,table_kind,number_of_seats,is_taken,table_area_id,table_position_id) values
 (1,1,1,'square',4,0,1,1);

Basically the idea is for 1 restaurant to be able to have more tables, and each table must have only one width,height and pos_x, pos_y. What am i doing wrong here?Is the table layout and constraints good? Any help would be awesome! Thanks!

Possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) — miken32, Feb 02 '17 at 21:04
It's not complaining that you're trying to add a new FK, it's saying that the data you're inserting will violate an existing FK constraint. Does your restaurants table have an entry with restaurant_id=1? How about table areas and position tables? You can't reference an ID in those tables from what you're trying to insert into restaurant_tables, unless that ID already exists in the tables marked with FK constraints. — A C, Feb 02 '17 at 21:14
in restaurants table i have one entry with restaurant_id=1 and name = bla, same goes for area and position table. Did that answer your question? I am sorry but i do not know much about sql except the basics, did not need much before... — vibetribe93, Feb 02 '17 at 21:24
Is that the whole error message, or is there more information about the specific tables and constraints at the end? — A C, Feb 02 '17 at 21:31
This is the whole thing: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`projectx`.`restaurant_tables`, CONSTRAINT `fk_restaurant_tables_restaurants` FOREIGN KEY (`restaurant_id`) REFERENCES `mydb`.`restaurants` (`restaurant_id`) ON DELETE NO ACTION ON UPDATE NO A) — vibetribe93, Feb 02 '17 at 21:45
Well, the error message says you don't have a row with `restaurant_id=1` in your `restaurants` tables. You should check (and then recheck again), because it is most likely the reason for your problem. Also you should recheck your data model. I would e.g. expect an area to belong to a specific restaurant, not stand on its own, and the position (if required to be in a seperate table at all) to maybe belong to a specific area. Also your primary key in `restaurant_tables` is not a proper primary key, since `id` is already a primary key on its own, and you don't seem to use `table_id` for anything. — Solarflare, Feb 02 '17 at 21:53
What do you mean by id is already primary key on its own? in restaurant_tables that one should be the primary key. Will recheck, and table_id is my internal thing. Thanks for the reply! — vibetribe93, Feb 02 '17 at 21:58

score 1 · Accepted Answer · answered Feb 25 '17 at 15:34

As indicated in Tables definition script, you have 3 FKs from restaurant_tables to 3 other tables. In order to your record to be valid, a record(row) could not exist in restaurant_tables, unless it meets 3 FK conditions:

because of restaurant_id FK from restaurant_tables to restaurants. there must be a row with restaurant_id = 1 in restaurants table. (regarding your insert code). otherwise the pointer to target table(restaurants) would not be able to refer to a valid row, thus making your record meaning less.
There must be a row with area_id = 1 in table_area regarding fk_restaurant_tables_table_area1 & your insert code.
There must be a row with position_id in table_position regarding fk_restaurant_tables_table_position1 & your insert code.

The bug arises here because of Foreign Key Constraints Violation & it has nothing to do with MySQL Workbench.

MySQL foreign key error while inserting into tables (error 1452), already created FKs

1 Answers1