CODEWITHSUNDEEP
mysqlsqlinsertnullcascade
Yelnar
Yelnar

Reputation: 674

Mysql. How to insert row to child table with null value of column that is foreign key

I have created two tables. First is 'buildings' and second is 'rooms'.

 CREATE TABLE buildings (
  building_no int(11) NOT NULL AUTO_INCREMENT,
  building_name varchar(255) NOT NULL,
  address varchar(355) NOT NULL,
  PRIMARY KEY (building_no)
 ) ENGINE=InnoDB;


 CREATE TABLE rooms (
  room_no int(11) NOT NULL AUTO_INCREMENT,
  room_name varchar(255) NOT NULL,
  building_no int(11) NULL,
  PRIMARY KEY (room_no),
  KEY building_no (building_no),
  CONSTRAINT rooms_ibfk_1 
  DEFAULT NULL
  FOREIGN KEY (building_no) 
  REFERENCES buildings (building_no) 
  ON DELETE RESTRICT
  ON UPDATE CASCADE
 ) ENGINE=InnoDB;

So when I add a new room with NULL column 'building_no' I get a message:

Cannot add or update a child row: a foreign key constraint fails (rooms, CONSTRAINT rooms_ibfk_1 FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON UPDATE CASCADE)

Why does this happen and how to solve it?

Upvotes: 2

Views: 1209

Answers (1)

Farshad
Farshad

Reputation: 1485

for create rooms table you can use this query 

CREATE TABLE IF NOT EXISTS `rooms` (
  `room_no` int(11) NOT NULL AUTO_INCREMENT,
  `room_name` varchar(255) NOT NULL,
  `building_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`room_no`),
  KEY `building_no` (`building_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

for insert in rooms instead of relation you must before make sure a primary key exist in building table so you can first run this query to insert on building : 

INSERT INTO `yourdb`.`buildings` (
`building_no` ,
`building_name` ,
`address`
)
VALUES (
NULL , 'b1', 'address of b1'
);

then for inserting in rooms table and define rooms for your specific building you can use this query :

INSERT INTO `yourdb`.`rooms` (
`room_no` ,
`room_name` ,
`building_no`
)
VALUES (
'1', 'room1', '1'
);

Upvotes: 1

Related Questions