Mysql创建表与多个外键删除设置null [英] Mysql create table with multiple foreign key on delete set null

问题描述

 
 
  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坦克，橡树桶），
 AgingRequirement int，
 PRIMARY KEY（葡萄）
）引擎= InnoDB; 
 
 CREATE TABLE葡萄园（
 VineyardID smallint auto_increment，
 VineyardName VARCHAR（45）NOT NULL，
 FarmerID smallint NOT NULL，
 GrapeID smallint NOT NULL， 
（1）ComeFrom varchar（45）NOT NULL，
 HarvestedAmount int，
 RipenessPercent int，
 PRIMARYID（VineyardID），
 FOREIGN KEY（FarmerID）REFERENCES Worker（WorkerID） 
 ON DELETE SET 
 ON UPDATE CASCADE，
 FOREIGN KEY（GrapeID）REFERENCES葡萄（葡萄）
 ON删除SET NULL 
 ON UPDATE CASCADE 
）发动机= InnoDB的; 
  

错误代码表示无法创建Vineyard表格，我只想知道正确的格式使用delete / update控件创建多个外键。

但是你的列定义是 NOT NULL 。

要么改变列定义，要么删除 NOT NULL 部分，或者过度地使用外键规则。这工作：
$ b $ pre $ code CREATE TABLE葡萄园（
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演示

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;

SQLFiddle demo

这篇关于Mysql创建表与多个外键删除设置null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！