Mysql在删除集null时创建具有多个外键的表

2022-01-20 00:00:00 sql foreign-keys mysql

我正在尝试使用具有删除/更新约束的多个外键创建一个数据库，但我收到了错误代码 1005，其中包含以下 sql 脚本:

I am trying to create a database with multiple foreign keys with delete/ update constraints, but I got a error code 1005 with following sql scripts:

CREATE TABLE Worker (
 WorkerID smallint auto_increment,
 WorkerType  varchar(45) NOT NULL,
 WorkerName  varchar(45) NOT NULL,
 Position    varchar(45) NOT NULL,
 TaxFileNumber int NOT NULL,
 Address    varchar(100) ,
 Phone      varchar(20) ,
 SupervisorID  smallint ,
 PRIMARY KEY (WorkerID),
 FOREIGN KEY (SupervisorID) REFERENCES Worker(WorkerID)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)Engine=InnoDB;

CREATE TABLE Grape (
    GrapeID smallint NOT NULL,
    GrapeType varchar(45) NOT NULL,
    JuiceConversionRatio int,
    StorageContainer ENUM('Stainless Steel Tank','Oak Barrel'),
    AgingRequirement int,
    PRIMARY KEY (GrapeID)
)Engine=InnoDB;

CREATE TABLE Vineyard (
    VineyardID smallint auto_increment,
    VineyardName VARCHAR(45) NOT NULL,
    FarmerID    smallint NOT NULL,
    GrapeID smallint NOT NULL,
    ComeFrom    varchar(45) NOT NULL,
    HarvestedAmount int,
    RipenessPercent int,
    PRIMARY KEY (VineyardID),
    FOREIGN KEY (FarmerID) REFERENCES Worker(WorkerID)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (GrapeID) REFERENCES Grape(GrapeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
)Engine=InnoDB;

错误代码说创建 Vineyard 表失败，我只想知道使用删除/更新控制创建多个外键的正确格式.

The error code says that fail to create the Vineyard table, I just want to know the proper format for creating multiple foreign keys with delete/update control.

推荐答案

你的外键规则是 ON DELETE SET NULL 但你的列定义是 NOT NULL.

Your foreign key rule is ON DELETE SET NULL but your column definition is NOT NULL.

要么更改列定义并删除 NOT NULL 部分，要么过度考虑外键规则.这行得通:

Either change your column definition and remove the NOT NULL part or overthink your foreign key rule. That works:

CREATE TABLE Vineyard (
    VineyardID smallint auto_increment,
    VineyardName VARCHAR(45) NOT NULL,
    FarmerID    smallint,
    GrapeID smallint,
    ComeFrom    varchar(45) NOT NULL,
    HarvestedAmount int,
    RipenessPercent int,
    PRIMARY KEY (VineyardID),
    FOREIGN KEY (FarmerID) REFERENCES Worker(WorkerID)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (GrapeID) REFERENCES Grape(GrapeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
)Engine=InnoDB;

SQLFiddle 演示

相关文章