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)

noting that a 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.

the output values could easily be changed to suit, the ones used were select based upon demonstrating rather than their subsequent use.

It is also possible to only insert a monster(s) if the reference(s) exist; consider the following that attempts to insert 4 monsters

monstera
monsterb 1.monsterx skipped because the origin (not a country or planet) points to neither a country or a planet
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:-

CASCADE being part of the in-built handling for referential integrity would have to catered for if needed.
i.e. monsterx and monsterY have been skipped due to the exclusion criteria.
- monsterx excluded because there is no reference,
- monstery excluded because of the ambiguity of Pluto being both a country and a planet.