Mysql在delete set 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:

我试图创建一个具有删除/更新约束的多个外键的数据库，但我得到一个错误代码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) ,
 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.

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

4 个解决方案

#1

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

您的外键规则是ON DELETE SET NULL但您的列定义是非NULL。

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

更改列定义并删除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 demo

SQLFiddle演示

#2

Try with create table(innoDB enginer) without foreign key and use the update table with constraint syntax, for example:

尝试使用没有外键的create table（innoDB enginer）并使用带有约束语法的更新表，例如：

ALTER TABLE `vineyard`
        ADD CONSTRAINT `relation_farmer_has_many_vineyards`
            FOREIGN KEY (`farmer_id`)
            REFERENCES `worker` (`worker_id`)
            ON DELETE SET NULL
            ON UPDATE CASCADE;

Reference:

参考：

• http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
• http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

Trick: is not recommended to use capital letters(or camel case) in the names of the tables that the behavior differs from the operating system being used:

技巧：不建议在行为与正在使用的操作系统不同的表的名称中使用大写字母（或驼峰大小写）：

• http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
• http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

#3

Visit :

访问：

https://developer.android.com/training/basics/data-storage/databases.html#DefineContract

https://developer.android.com/training/basics/data-storage/databases.html#DefineContract

Cursor c = db.query(
    FeedEntry.TABLE_NAME,                     // The table to query
    projection,                               // The columns to return
    selection,                                // The columns for the WHERE clause
    selectionArgs,                            // The values for the WHERE clause
    null,                                     // don't group the rows
    null,                                     // don't filter by row groups
    sortOrder                                 // The sort order
    );

#4

Visit :

访问：

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

CREATE TABLE `ffxi_characterJob` (
`serverID` int(11) NOT NULL,
`userid` int(10)unsigned NOT NULL,
`characterName` varchar(255) NOT NULL,
`jobAbbr` char(4) NOT NULL,
`jobLevel` int(11) default '0',
PRIMARY KEY (`serverID`,`userid`,`characterName`,`jobAbbr`),
INDEX (`jobAbbr`),
CONSTRAINT FOREIGN KEY (`serverID`,`userid`,`characterName`) REFERENCES `ffxi_characters` (`serverID`,`userid`,`characterName`) 
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (`jobAbbr`) REFERENCES `ffxi_jobType` (`jobAbbr`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;