mysql多个外键删除设置,Mysql创建表与多个外键删除设置null

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;

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.

解决方案

Your foreign key rule is ON DELETE SET NULL but your column definition is 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;