MySQL (wampserver phpmyadmin) 支持 ON DELETE

Question

我创建了两个表 buildings 和 rooms。

如果我删除建筑物，我希望房间也被删除。

我知道这可以通过使用外键约束和使用ON DELETE CASCADE

来实现

这是我的做法。

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

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134');

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);

执行删除时，删除不会级联。是 phpmyadmin 的问题还是我的代码有什么问题(这种可能性很小)？

phpmyadmin 也支持 ON DELETE 语句。如果不是我应该使用什么。

注意:我不想删除并重新创建整个表

Answer 1

在您的例子中，默认存储引擎设置为 MyISAM。 MyISAM 不支持外键约束。来自 Docs :

For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

创建表时，可以显式指定which storage engine to use对于表。一种常见的选择是 InnoDB(使用 ENGINE = INNODB)。您可以使用以下查询来创建表:

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

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
) ENGINE = INNODB;

如果您的表已经创建，并且您希望更改引擎(而不是再次创建新表)，您可以使用 Alter Table命令。

ALTER TABLE buildings ENGINE = InnoDB;
ALTER TABLE rooms ENGINE = InnoDB;