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

Question

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?

I can't reproduce your error. When I ran your code, I first removed the lone DEFAULT NULL line in the "rooms" table definition because it yielded a syntax error and isn't relevant to your apparent problem. After this, adding a new record with a NULL value for 'building_no' to the "rooms" table yielded no error. Your problem is strange because NULL values *can* generally be added to a foreign key: [https://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate] — Ethan Powell, Aug 12 '19 at 01:27

Farshad · Answer 1 · 2014-08-12T12:29:39.980

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'
);

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

1 Answers1