php - SQL中的一对多关系 - 外键错误

Question

我正在尝试创建一个校园结构。所以建筑物有楼层，楼层有房间。我正在尝试创建一个关系数据库，以便多个房间与一层相关，多个楼层与其建筑物相关。

这是我的建筑物和地板 table 的结构:

CREATE TABLE `building` (
 `id` int(11) NOT NULL,
 `name` varchar(128) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `floor` (
 `id` int(11) NOT NULL,
 `building_id` int(11) DEFAULT NULL,
 `level` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `floor_building_id__fk` (`building_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

我想在与同一 building_id 相关的楼层表中插入多个楼层，使用:

INSERT INTO `floor` SET id=3, `number` = 420, building_id=(SELECT id FROM building WHERE id=2);

但是我不断收到以下错误:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`seatspace`.`floor`, CONSTRAINT `building_id` FOREIGN KEY (`id`) REFERENCES `building` (`id`))

我想要插入、更新和删除与其指定的 building_id 相关的楼层。任何帮助将不胜感激。

Answer 1

我通过更改外键的约束名称解决了该问题。我还重写了部分架构，以便所有所需的列都存在。这是最终的架构和 CRUD。

CREATE TABLE `floor` (
  `floor_id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `building_id` int(11) NOT NULL,
  PRIMARY KEY (`floor_id`),
  UNIQUE KEY `floor_id_UNIQUE` (`floor_id`),
  KEY `building_id_idx` (`building_id`),
  CONSTRAINT `building_id` FOREIGN KEY (`building_id`) REFERENCES `building` 
(`building_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

INSERT INTO `floor` (floor_id, `number`, building_id) VALUES (default, 3, 
(SELECT building_id FROM building WHERE building_id=2)) ;
UPDATE `floor` SET `number`=3, building_id=1 WHERE floor_id=2;
DELETE FROM `floor` WHERE floor_id=3;

CREATE TABLE `building` (
  `building_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`building_id`),
  UNIQUE KEY `building_id_UNIQUE` (`building_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

INSERT INTO building (building_id, name) VALUES (DEFAULT, '1 West');
UPDATE `building` SET `name`='3 West' WHERE building_id=2;
DELETE FROM `building` WHERE building_id=2;