How to get a list of tables that have one-on-one relationship to a given table in SQLite3?

Question

Is there a way to get a list of tables that have one-on-one relationship to a given table in SQLite3?

For example, here table ab has a one-on-one relationship with both table abc and abd. Is there a query or queries to return abc and abd for the given table name ab?

-- By default foreign key is diabled in SQLite3
PRAGMA foreign_keys = ON; 

CREATE TABLE a (
    aid          INTEGER      PRIMARY KEY
);

CREATE TABLE b (
    bid          INTEGER      PRIMARY KEY
);

CREATE TABLE ab (
    aid          INTEGER,
    bid          INTEGER,
    PRIMARY KEY (aid, bid)
    FOREIGN KEY (aid)  REFERENCES a(aid)
    FOREIGN KEY (bid)  REFERENCES b(bid)
);

-- tables 'ab' and 'abc' have a one-on-one relationship
CREATE TABLE abc (
    aid          INTEGER,
    bid          INTEGER,
    name         TEXT          NOT NULL,
    PRIMARY KEY (aid, bid)  FOREIGN KEY (aid, bid)  REFERENCES ab(aid, bid)
);

-- tables 'ab' and 'abd' have a one-on-one relationship
CREATE TABLE abd (
    aid          INTEGER,
    bid          INTEGER,
    value        INTEGER       CHECK( value > 0 ),
    PRIMARY KEY (aid, bid)  FOREIGN KEY (aid, bid)  REFERENCES ab(aid, bid)
);

CREATE TABLE w (
    id           INTEGER      PRIMARY KEY
);

The following tedious precedure may get me the list of tables I want:

1. Get primary keys for table ab:

   SELECT l.name FROM pragma_table_info('ab') as l WHERE l.pk > 0;

2. get foreign keys for other tables (this case is for table abd):

   SELECT * from pragma_foreign_key_list('abd');

3. Do parsing to get what the list of tables of one-on-one relationships.

However, there must exist a more elegant way, I hope.

For SQL Server, there are sys.foreign_keys and referenced_object_id avaible (see post). Maybe there is something similar to that in SQLite?

Edit: adding two more tables for test

-- tables 'ab' and 'abe' have a one-on-one relationship
CREATE TABLE abe (
    aid          INTEGER,
    bid          INTEGER,
    value        INTEGER       CHECK( value < 0 ),
    PRIMARY KEY (aid, bid)  FOREIGN KEY (aid, bid)  REFERENCES ab
);

-- tables 'ab' and 'abf' have a one-on-one relationship
CREATE TABLE abf (
    aidQ          INTEGER,
    bidQ          INTEGER,
    value        INTEGER,
    PRIMARY KEY (aidQ, bidQ)  FOREIGN KEY (aidQ, bidQ)  REFERENCES ab(aid, bid)
);

Edit: verify FK for table abe

sqlite> PRAGMA foreign_keys;
1
sqlite> .schema abe
CREATE TABLE abe (
    aid          INTEGER,
    bid          INTEGER,
    value        INTEGER       CHECK( value < 0 ),
    PRIMARY KEY (aid, bid)  FOREIGN KEY (aid, bid)  REFERENCES ab
);
sqlite> DELETE FROM abe;
sqlite> INSERT INTO abe (aid, bid, value) VALUES (2, 1, -21);
sqlite> INSERT INTO abe (aid, bid, value) VALUES (-2, 1, -21);
Error: FOREIGN KEY constraint failed
sqlite> SELECT * FROM ab;
1|1
1|2
2|1

Answer 1

Alternative

Although not a single query solution the following only requires submission/execution of a series of queries and is therefore platform independent.

It revolves around using two tables:-

• a working copy of sqlite_master
• a working table to store the the output of SELECT pragma_foreign_key_list(?)

Both tables are created via a CREATE-SELECT, although neither has any rows copied, so the tables are empty.

A trigger is applied to the working copy of sqlite_master to insert into the table that stores the result of SELECT pragma_foreign_key_list(table_name_from_insert);

The relevant rows are copied from sqlite_master via a SELECT INSERT and thus the triggering populates the store table.

The following is the testing code :-

DROP TABLE IF EXISTS fklist;
DROP TABLE IF EXISTS master_copy;
DROP TRIGGER IF EXISTS load_fklist;
/* Working version of foreign_key_list to store ALL results of SELECT pragma_foreign_key_list invocation */
CREATE TABLE IF NOT EXISTS fklist AS SELECT '' AS child,* 
    FROM pragma_foreign_key_list((SELECT name FROM sqlite_master WHERE type = 'not a type' LIMIT 1));
/* Working version of sqlite master */
CREATE TABLE IF NOT EXISTS master_copy AS SELECT * FROM sqlite_master WHERE type = 'not a type';
/* Add an after insert trigger for master copy to add to fklist */
CREATE TRIGGER IF NOT EXISTS load_fklist 
    AFTER INSERT ON master_copy
    BEGIN
        INSERT INTO fklist SELECT new.name,* FROM pragma_foreign_key_list(new.name);
    END
;

/* Populate master_copy from sqlite_master (relevant rows)
    and thus build the fklist
*/
INSERT INTO master_copy SELECT * 
    FROM sqlite_master 
    WHERE type = 'table' 
        AND instr(sql,' REFERENCES ') > 0
;
SELECT * FROM fklist;
DROP TABLE IF EXISTS fklist;
DROP TABLE IF EXISTS master_copy;
DROP TRIGGER IF EXISTS load_fklist;

Using a similar test base as per the previous answer the above results in :-