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

CodePudding user response：

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

Not with certainty as coding a Foreign Key constraint does not define a relationship (rather it supports a relationship), that is relationships can exists without a FK constraint.

A Foreign Key constraint defines:-

• a) a rule that enforces referential integrity
• b) optionally maintains/alters referential integrity when the referred to column is changed (ON DELETE and ON UPDATE )

As such looking at the Foreign Key List only tells you where/if a FK constraint has been coded.

Saying that the following will get the tables with the constraint and the referenced tables.

More elegant is a matter of opinion, so it's up to you :-

WITH cte_part(name,reqd,rest) AS (
    SELECT name,'',substr(sql,instr(sql,' REFERENCES ')   12)||' REFERENCES '
    FROM sqlite_master 
    WHERE sql LIKE '% REFERENCES %(%'
    UNION ALL
    SELECT 
        name,
        substr(rest,0,instr(rest,' REFERENCES ')),
        substr(rest,instr(rest,' REFERENCES ')   12)
    FROM cte_part
    WHERE length(rest) > 12 
)
SELECT DISTINCT
    CASE
        WHEN length(reqd) < 1 THEN name
        ELSE 
            CASE substr(reqd,1,1)
                WHEN '''' THEN substr(replace(reqd,substr(reqd,1,1),''),1,instr(reqd,'(')-3)
                WHEN '[' THEN substr(replace(replace(reqd,'[',''),']',''),1,instr(reqd,'(')-3)
                WHEN '`' THEN substr(replace(reqd,substr(reqd,1,1),''),1,instr(reqd,'(')-3)
                ELSE  substr(reqd,1,instr(reqd,'(')-1)
            END
    END AS tablename
FROM cte_part
;

As an example of it's use/results :-

• screenshot from Navicat

Here's an adaptation of the above that includes, where appropriate, the child table that references the parent :-

WITH cte_part(name,reqd,rest) AS (
    SELECT name,'',substr(sql,instr(sql,' REFERENCES ')   12)||' REFERENCES '
    FROM sqlite_master 
    WHERE sql LIKE '% REFERENCES %(%'
    UNION ALL
    SELECT 
        name,
        substr(rest,0,instr(rest,' REFERENCES ')),
        substr(rest,instr(rest,' REFERENCES ')   12)
    FROM cte_part
    WHERE length(rest) > 12 
)
SELECT DISTINCT
    CASE
        WHEN length(reqd) < 1 THEN name
        ELSE 
            CASE substr(reqd,1,1)
                WHEN '''' THEN substr(replace(reqd,substr(reqd,1,1),''),1,instr(reqd,'(')-3)
                WHEN '[' THEN substr(replace(replace(reqd,'[',''),']',''),1,instr(reqd,'(')-3)
                WHEN '`' THEN substr(replace(reqd,substr(reqd,1,1),''),1,instr(reqd,'(')-3)
                ELSE  substr(reqd,1,instr(reqd,'(')-1)
            END
    END AS tablename,
    CASE WHEN length(reqd) < 1 THEN '' ELSE name END AS referrer
FROM cte_part
;

Example of the Result :-

• the artists table is referenced by albums as the SQL used to create the albums table is CREATE TABLE 'albums'([AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,[Title] TEXT NOT NULL ,[ArtistId] INTEGER NOT NULL , FOREIGN KEY ([ArtistId]) REFERENCES 'artists'([ArtistId]))

• i.e. FOREIGN KEY ([ArtistId]) REFERENCES 'artists'([ArtistId]))

• the employees table is self-referencing as per CREATE TABLE 'employees'(.... REFERENCES 'employees'([EmployeeId]))

Additional re comment:-

(I am still trying to understand your code...)

The code is based upon selecting rows from sqlite_master where the row is for a table (type = 'table'), as opposed to an index, trigger or view and where the sql column contains the word REFERENCES with a space before and after and there is a following left parenthesis.

• The last condition used to weed out the likes of CREATE TABLE oops (`REFERENCES` TEXT, `x REFERENCES Y`);

For each selected row 3 columns are output:-

1. name which is the name of the table as extracted from the name column of sqlite_master,
2. reqd is initially an empty string (i.e. initial)
3. rest the rest of sql that follows the referred to table name with suffixed with REFERENCES.

The UNION ALL adds rows that are built upon what is newly added to the CTE, i.e. the three columns are extracted as per :-

1. name is the name
2. reqd is the sql from the rest column up until the first REFERENCES term (i.e. the table and referenced column(s))
3. rest is the sql from after the REFERENCES term

As with any recursion the end needs to be detected, this is when the entire sql statement has been reduced to being less than 12 (i.e the length of " REFERENCES ", the term used for splitting the sql statement).

This is what is termed as a

As can clearly be seen the extracted sql progressively reduces

The answer is intended as in-principle and has not been extensively tested to consider all scenarios, it may well need tailoring.