Geek asks and answers

Mysql creates a table with several foreign keys when deleting set null

I am trying to create a database with several foreign keys with delete / update restrictions, but I got error code 1005 with the 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 it is not possible to create a Vineyard table, I just want to know the correct format for creating several foreign keys with delete / update control.

+5
source share
4 answers

The rule of your foreign key ON DELETE SET NULL, but the definition of the column NOT NULL.

Modify the column definition and delete the part NOT NULLor override the foreign key rule. It 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

+13
source

(innoDB enginer) update table with constraint, :

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;

:

Trick: ( ) , :

0

Visit:

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
    );
0
source

Visit:

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;
-1
source

More articles:


All Articles