MySQL ON DELETE CASCADE无效

Question

我有两个表(类型为innoDb)，为什么我删除主表行时如下所示

+note(我使用的是arch os，所以我的数据库服务器类型是maria db)

DELETE FROM buildings 
WHERE
    building_no = 2;

关系表行不要删除？

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

Answer 1

此语法适用于MySQL数据库，但在您的查询中也看不到问题

CREATE TABLE  `rooms` (
  room_no INT PRIMARY KEY AUTO_INCREMENT,
  room_name VARCHAR(255) NOT NULL,
  building_no INT NOT NULL,
  CONSTRAINT `FK_rooms_1` FOREIGN KEY (`building_no`) REFERENCES `buildings` 
  (`building_no`) ON DELETE CASCADE
) ENGINE=InnoDB;

或者在创建表之后添加约束

ALTER TABLE `rooms`
  ADD CONSTRAINT `FK_rooms_1` FOREIGN KEY (`building_no`) REFERENCES `buildings` (`building_no`) ON DELETE CASCADE ON UPDATE CASCADE;