CODEWITHSUNDEEP
mysqlforeign-keysmariadb
Jungar
Jungar

Reputation: 49

Table creation - foreign key constraint incorrectly formed

I'm creating a database for a zoo for a project, and when I try to create the table "Lives" with a primary key from the table "Animals" i get an error

CREATE TABLE Department
    (DeptName       VARCHAR(35),
     DeptBuilding   VARCHAR(35),
     DeptBudget     VARCHAR(9),
     PRIMARY KEY(DeptName)
    );

CREATE TABLE Animals
    (AnimalID       CHAR(5),
     NameLatin      VARCHAR(35),
     DeptName       VARCHAR(35),
     AnimalType     VARCHAR(10),
     AnimalName     VARCHAR(10),
     PRIMARY KEY(AnimalID),
     FOREIGN KEY(DeptName) REFERENCES Department(DeptName) ON DELETE SET NULL
    );

CREATE TABLE Houses
    (HouseID        CHAR(5),
     HouseName      VARCHAR(35),
     TypeHouse      VARCHAR(10),
     DeptName       VARCHAR(35),
     PRIMARY KEY(HouseID), 
     FOREIGN KEY(DeptName) REFERENCES Department(DeptName) ON DELETE SET NULL
    );

CREATE TABLE Lives
    (AnimalID       CHAR(5),
     HouseID        CHAR(5),
     PRIMARY KEY(AnimalID, HouseID),
     FOREIGN KEY(AnimalID) REFERENCES Animals(AnimalID) ON DELETE SET NULL,
     FOREIGN KEY(HouseID) REFERENCES Houses(HouseID) ON DELETE SET NULL
    );

I expected it to create the table but i get: "Foreign key constraint is incorrectly formed"

Upvotes: 0

Views: 25

Answers (1)

spencer7593
spencer7593

Reputation: 108410

I think the problem is the ON DELETE SET NULL.

Given that AnimalID is a column in the PRIMARY KEY of Lives, it's not possible to specify the setting the column to NULL. (The PRIMARY KEY constraint enforces a NOT NULL restriction on the columns in the primary key.)

Try ON DELETE RESTRICT. And then give it a whirl with ON DELETE CASCADE.

If we omit the ON DELETE and ON UPDATE clauses, the default action is NO ACTION, which as far as I can tell operate exactly as if we had specified RESTRICT.

Upvotes: 1

Related Questions