I have a table in an Sqlite database which is currently a foreign key to a field in another table. However, I need to change that foreign key so that it can point to more than one table.
In the example below, initially I was only dealing with monsters with an Origin in some country on Earth. Now, I want to add the possibility that the monster comes from a different planet. Is there a way to do this without adding an intermediate table or renaming tables or adding an additional field to the "Monster" table, or to any other table ?
I would like to do it just by modifying the foreign key.
CREATE TABLE Monster (
Id INTEGER PRIMARY KEY,
Name VARCHAR NOT NULL UNIQUE,
Origin TEXT,
FOREIGN KEY (Origin) REFERENCES Country(Name) ON UPDATE CASCADE
);
CREATE TABLE Country (
Id INTEGER PRIMARY KEY,
Name VARCHAR NOT NULL UNIQUE,
);
However, I have just discovered that the monster may also come from another planet and that therefore the "Origin" column needs to reference either the "Country" table or the "Planet" table.
CREATE TABLE Planet (
Id INTEGER PRIMARY KEY,
Name VARCHAR NOT NULL UNIQUE,
);
Note, that I have a similar situation with a different set of tables, but is resolved by using virtual columns.
CREATE MonsterElectricityPower (
Id INTEGER PRIMARY KEY,
Name VARCHAR NOT NULL UNIQUE
);
CREATE MonsterFreezePower (
Id INTEGER PRIMARY KEY,
Name VARCHAR NOT NULL UNIQUE
);
CREATE MonsterPowerParameter (
Id INTEGER PRIMARY KEY,
Level INTEGER,
ParentCode TEXT,
ParentName TEXT,
ParentCode$MonsterElectricityPower TEXT generated always as (case when ParentCode = 'MonsterElectricityPower' then ParentName else NULL end) virtual REFERENCES MonsterElectricityPower (Name) ON DELETE CASCADE,
ParentCode$MonsterFreezePower TEXT generated always as (case when ParentCode = 'MonsterFreezePower' then ParentName else NULL end) virtual REFERENCES MonsterFreezePower(Name) ON DELETE CASCADE
);
Here, the ParentCode indicates what table/column needs to be referenced and so I can create my virtual column accordingly.
However, in the first case above, I have no way from the Monster table to know whether the Origin column should reference a record in the Country table or in the Planet table.
How to have a foreign key to more than one table/field in sqlite
This is not possible with SQLite for a single Foreign Key constraint and if 2 were used then both would need to be matched (the ability to match to NULL would probably just complicate matters)
Foreign Key
definition as coded is NOT the definition of/for a foreign key BUT is actually the definition of a constraint rule that supports referential integrity. That is you can have and use a foreign key without defining a constraint (see the demos below that utilise foreign keys albeit it without enforcement of referential integrity).I have no way from the Monster table to know whether the Origin column should reference a record in the Country table or in the Planet table.
You do.
Consider the following (based upon the tables in your question):-
INSERT INTO country (name) VALUES
('England'),('Australia'),('France'),('Spain'),('Germany'),('Pluto') /*<<<< PLUTO PURPOSEFULLY AMBIGUOUS */
;
INSERT INTO planet (name) VALUES
('Mars'),('Saturn'),('Jupiter'),('Venus'),('Pluto') /*<<<< AGAIN PLUTO PURPOSEFULLY AMBIGUOUS */
;
INSERT INTO Monster (name,origin) VALUES
('Monster 1','Australia')
,('Monster 2','Venus')
,('Monster 3','Pluto')
, ('Monster from nowhere!!!!','Timbuktu')
;
SELECT
monster.*,
/* know whether planet, country,both (country and planet), or neither(nowhere) */
CASE
WHEN origin IN (SELECT name FROM country) AND origin IN (SELECT name from planet) THEN 'country and planet'
WHEN origin IN (SELECT name FROM country) THEN 'country'
WHEN origin IN (SELECT name FROM planet) THEN 'Planet'
ELSE 'nowhere'
END AS type
/* get both together */
,coalesce((SELECT name FROM country AS c WHERE c.name=origin),'')||coalesce((SELECT name FROM planet AS p WHERE p.name=origin),'') AS combined
/* get both individually */
,(SELECT name FROM country AS c WHERE c.name=origin) AS country_of_origin
,(SELECT name FROM planet AS p WHERE p.name=origin) AS planet_of_origin
FROM Monster;
This results in:-
So it is possible to ascertain which, both or none.
It is also possible to only insert a monster(s) if the reference(s) exist; consider the following that attempts to insert 4 monsters
not a country or planet
) points to neither a country or a planetas per:-
WITH
cte_insert_data(monster,origin) AS (
SELECT 'monsterx','not a country or planet'
UNION SELECT 'monstera','England'
UNION SELECT 'montserb','Venus'
UNION SELECT 'monstery','Pluto'
),
cte_to_insert_monster(monster,origin,in_country,in_planet) AS (
SELECT
monster,
origin,
origin IN (SELECT name FROM country AS c WHERE c.name=origin),
origin IN (SELECT name FROM planet AS p WHERE p.name=origin)
FROM cte_insert_data
)
INSERT INTO monster (name,origin)
SELECT monster,origin
FROM cte_to_insert_monster
WHERE (in_country OR in_planet)
AND NOT (in_country AND in_planet) /* to exclude insertion of ambiguous (Pluto) if required */;
SELECT * FROM monster;
The result of the ensuing SELECT being:-