SQLite foreign key mismatch when trying to insert null value

Question

I have the following tables:

CREATE TABLE "Abilities" (
"ID"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
"Name"  TEXT NOT NULL,
"Description"   TEXT NOT NULL
)

and

CREATE TABLE "Creatures" (
"ID"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
"Faction"   TEXT,
"Name"  TEXT NOT NULL UNIQUE,
"Tier"  INTEGER NOT NULL DEFAULT 1,
"Upgrade"   INTEGER NOT NULL DEFAULT 0,
"GoldCost"  INTEGER NOT NULL DEFAULT 0,
"PopulationWeekly"  INTEGER NOT NULL DEFAULT 0,
"Attack"    INTEGER NOT NULL DEFAULT 0,
"Defense"   INTEGER NOT NULL DEFAULT 0,
"DamageMin" INTEGER NOT NULL DEFAULT 0,
"DamageMax" INTEGER NOT NULL DEFAULT 0,
"Initiative"    INTEGER NOT NULL DEFAULT 0,
"Speed" INTEGER NOT NULL DEFAULT 0,
"Health"    INTEGER NOT NULL DEFAULT 0,
"Mana"  INTEGER NOT NULL DEFAULT 0,
"Shots" INTEGER NOT NULL DEFAULT 0,
"Experience"    INTEGER NOT NULL DEFAULT 0,
"PowerRating"   INTEGER NOT NULL DEFAULT 0,
"Abilities" TEXT,
FOREIGN KEY("Abilities") REFERENCES "Abilities"("Name")
)

When trying to do the following insert:

INSERT INTO "main"."Creatures"
("Faction", "Name", "Abilities")
VALUES ('Academy', 'Gremlin', null);

I get a:

Result: foreign key mismatch - "Creatures" referencing "Abilities"
At line 1:
INSERT INTO "main"."Creatures"
("Faction", "Name", "Abilities")
VALUES ('Academy', 'Gremlin', null);

error. But I thought it would be OK if I try to insert a 'null' into a foreign key? In this case I want to be able to insert empty values for some "Ability" values, since not all records must have an "Ability" value.

How do I make it so that whenever something is inserted into "Creatures" SQL would check if "Ability" IS NOT NULL then the record must exist in the "Name" column of the "Abilities" table.

forpas forpas · Accepted Answer · 2019-11-20T18:22:32

The problem is that the referenced column Name in the table Abilities is not the PRIMARY KEY of the table (why do you want the name in Creatures and not the id which is the usual practice?) and it is not either UNIQUE.
If the referenced column is not UNIQUE you can't insert any value in the column Abilities of the table Creatures and not only NULL.

Change to this:

CREATE TABLE "Abilities" (
    "ID"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "Name"  TEXT NOT NULL UNIQUE,
    "Description"   TEXT NOT NULL
)

Now you will be able to insert NULL values in the column Abilities of the table Creatures.

But the best thing to do is to have an integer column like Abilities_Id in Creatures, referencing the PRIMARY KEY of the table Abilities instead of Name.

You can find more here: SQLite Foreign Key Support

SQLite foreign key mismatch when trying to insert null value

1 Answers