mysql - 使用外键创建表的 SQL 语句不正确？

Question

我不确定为什么 SQL 不适用于 Class 表。我知道问题与外键行有关，如果我删除该表将被创建。我的语法有问题吗？

我收到的错误是:错误代码:1005。无法创建表“university.class”(错误号:150)

CREATE TABLE Faculty (
    facId VARCHAR(6),
    name VARCHAR(20) NOT NULL,
    department VARCHAR(20),
    rank VARCHAR(10),
    CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));

DROP TABLE Class;

CREATE TABLE Class (
    classNumber VARCHAR(8),
    facId VARCHAR(6) NOT NULL,
    schedule VARCHAR(8),
    room VARCHAR(6),
    CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),
    CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE SET NULL,
    CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));

Answer 1

您有 ON DELETE SET NULL 但 Class.facID 不允许空值，所以这是不可能的。

This works并删除父记录，将 NULL 列留在孤儿中。

CREATE TABLE Faculty (
    facId VARCHAR(6),
    name VARCHAR(20) NOT NULL,
    department VARCHAR(20),
    rank VARCHAR(10),
    CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));



CREATE TABLE Class (
    classNumber VARCHAR(8),
    facId VARCHAR(6) NULL,
    schedule VARCHAR(8),
    room VARCHAR(6),
    CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),
    CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE set null,
    CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));


INSERT INTO Faculty 
VALUES('1','Foo','foo','foo');


INSERT INTO Class 
VALUES('1','1','foo','foo');

DELETE FROM Faculty;