sqlite

How to have a foreign key to more than one table/field in sqlite

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.

Solution

  • 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)

    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:-

    Result

    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

    1. monstera
    2. monsterb 1.monsterx skipped because the origin (not a country or planet) points to neither a country or a planet
    3. monstery skipped due to the (optional) exclusion of an ambiguous origin (i.e. Pluto)

    as 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:-

    enter image description here