Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql create table with multiple foreign key on 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:

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.

like image 718
Tim Raynor Avatar asked Apr 08 '13 13:04

Tim Raynor


People also ask

Can foreign key have multiple null values?

Foreign keys allow key values that are all NULL , even if there are no matching PRIMARY or UNIQUE keys. By default (without any NOT NULL or CHECK clauses), the FOREIGN KEY constraint enforces the match none rule for composite foreign keys in the ANSI/ISO standard.

Can we have multiple foreign keys in a table in MySQL?

A table can have multiple foreign keys based on the requirement.

Is set to null a delete rule?

A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.


1 Answers

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

like image 74
juergen d Avatar answered Oct 05 '22 03:10

juergen d