CODEWITHSUNDEEP
mysqldatabaseforeign-keysmysql-workbench
Noob
Noob

Reputation: 11

cant insert data into child table MySQL

This is quite a common problem i have and havent figured out the solution yet, nor have i found an online solution and i have been searching for some days (if someone knows of one, just giving me the link would be much appreciated).

I have created a database using MySQL Workbench, server with wamp and going to use netbeans ide 8.0 to create a php app for my project.

My problem is that i cant add data in "child" (excuse me is the term is not accurate) tables. The folowwing code comes from "forward engineering sql code from eer model" of MySQL Workbench and regards only one of the child tables i have problems with:

CREATE TABLE IF NOT EXISTS `ASTIR`.`HOTEL` (
`ID` INT NOT NULL,
`NAME` VARCHAR(45) NOT NULL,
`STREET` VARCHAR(45) NULL,
`No` INT NULL,
`ΤΚ` INT NULL,
`CITY` VARCHAR(45) NULL,
`RATING` SMALLINT NULL,
`YEAR OF CONSTRUCTION` YEAR NULL,
`YEAR OF RENOVATION` YEAR NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `ASTIR`.`ROOM` (
`HOTEL_ID` INT NOT NULL,
`ROOM_No` INT NOT NULL,
`TYPE` VARCHAR(45) NOT NULL,
`PRICE` DOUBLE NOT NULL,
PRIMARY KEY (`HOTEL_ID`, `ROOM_NO`),
INDEX `fk_ROOM_HOTEL1_idx` (`HOTEL_ID` ASC),
CONSTRAINT `fk_ROOM_HOTEL1`
FOREIGN KEY (`HOTEL_ID`)
REFERENCES `ASTIR`.`HOTEL` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

So, when i type the command

INSERT INTO ROOM(HOTEL_ID,ROOM_No,TYPE,PRICE) VALUES(1,1,'SUITE',55);

I get the message

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`ASTIR`.`ROOM`, CONSTRAINT `fk_ROOM_HOTEL1` FOREIGN KEY (`HOTEL_ID`) REFERENCE)

Any ideas would help, thanks a lot :/

Upvotes: 1

Views: 6264

Answers (2)

dsanatomy
dsanatomy

Reputation: 533

Maybe try disabling the foreign key before you run the query and then enable it again after you're done.Can you add the data manually directly to the created table (without coding) ? .To disable the foreign key

SET FOREIGN_KEY_CHECKS=0

Enable:

SET FOREIGN_KEY_CHECKS=1

Also : PRIMARY KEY (HOTEL_ID, ROOM_NO)

Why not set only the ROOM_NO to be primary key? What you did is usually used for an n:n relationship, where there is usually an intermediate table.

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

That error implies that there is no row in the HOTEL table with id=1.

You need to insert the parent (HOTEL) rows first before you can insert the child (ROOM) rows.

INSERT INTO HOTEL(ID,NAME) VALUES(1,'First Hotel');
INSERT INTO ROOM(HOTEL_ID,ROOM_No,TYPE,PRICE) VALUES(1,1,'SUITE',55);

Upvotes: 2

Related Questions