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

本教程将介绍Mysql在删除集null时创建具有多个外键的表的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

Mysql在删除集null时创建具有多个外键的表 教程 第1张

问题描述

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

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) ,
 Phonevarchar(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 表失败,我只想知道使用删除/更新控制创建多个外键的正确格式.

推荐答案

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

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

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 演示

好了关于Mysql在删除集null时创建具有多个外键的表的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。