如何獲取與SQLite3中給定表具有一對一關系的表串列？

有沒有辦法獲取與 SQLite3 中給定表具有一對一關系的表串列？

例如，這里的 tableab與 tableabc和abd. 是否有一個查詢或查詢來回報abc和abd對于給定的表名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
);

以下乏味的程式可能會讓我得到我想要的表串列：

1. 獲取表的主鍵ab：

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

2. 獲取其他表的外鍵（本例適用于 table abd）：

   SELECT * from pragma_foreign_key_list('abd');

3. 進行決議以獲取一對一關系的表串列。

但是，我希望必須存在一種更優雅的方式。

對于 SQL Server，有sys.foreign_keys并且referenced_object_id可用（請參閱

uj5u.com熱心網友回復：

有沒有辦法獲取與 SQLite3 中給定表具有一對一關系的表串列？

不確定，因為對外鍵約束進行編碼并沒有定義關系（而是支持關系），即關系可以在沒有 FK 約束的情況下存在。

外鍵約束定義：-

• a) 強制執行參照完整性的規則
• b) 當參考的列更改時，可選擇維護/更改參考完整性（ON DELETE和ON UPDATE）

因此，查看外鍵串列只會告訴您在何處/是否已對 FK 約束進行編碼。

說下面將獲得帶有約束的表和參考的表。

更優雅是見仁見智，所以這取決于你：-

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
;

作為它的使用/結果的一個例子：-

• Navicat 的截圖

這是對上述內容的改編，在適當的情況下包括參考父表的子表：-

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
;

結果示例：-

• 在藝術家表被參考的專輯作為SQL用來創建相冊表CREATE TABLE 'albums'([AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,[Title] TEXT NOT NULL ,[ArtistId] INTEGER NOT NULL , FOREIGN KEY ([ArtistId]) REFERENCES 'artists'([ArtistId]))

• IE FOREIGN KEY ([ArtistId]) REFERENCES 'artists'([ArtistId]))

• 該雇員表是自參考按CREATE TABLE 'employees'(.... REFERENCES 'employees'([EmployeeId]))

補充評論：-

（我仍在嘗試理解您的代碼...）

該代碼基于從 sqlite_master 中選擇行，其中該行用于表（type = 'table'），而不是索引、觸發器或視圖，其中 sql 列包含單詞 REFERENCES，前后有空格是后面的左括號。

• 最后一個條件用于清除諸如 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

答案是原則上的，尚未經過廣泛測驗以考慮所有情況，很可能需要量身定制。

標籤：sqlite 外键 关系型数据库

上一篇：SQLiteDataReader似乎保持資料庫連接打開

下一篇：在不使用擴展的情況下動態轉置SQLite中的行