【超解説】SQLiteの外部キー制約：名前の取得からトラブル解決まで

2024-05-18

SQLite における外部キー制約の名前を取得する方法

PRAGMA TABLE_INFO を使用する

この方法は、テーブル内のすべての情報を含むため、目的の外部キー制約を見つけるためにデータをフィルタリングする必要があります。

PRAGMA TABLE_INFO([テーブル名]);

出力結果には、列名、データ型、主キー制約情報などが含まれます。外部キー制約の情報は FOREIGN KEY 列に記載されています。

CID     NAME      TYPE        NOT NULL    DEFAULT  PRIMARY KEY    FOREIGN KEY
-------  ---------  -----------  ---------  --------  ------------  ------------
1       id         INTEGER     NOT NULL         PRIMARY KEY     REFERENCES parent_table(id)
2       name       TEXT        NOT NULL

上記の例では、id 列は parent_table テーブルの id 列を参照する外部キー制約を持っています。

SQLite データベースブラウザツールを使用する

SQLite Studio や DBeaver など、視覚的にデータベースを操作できるツールを使用すると、外部キー制約とその名前を簡単に確認できます。

補足

SQLite 3.18.0 以降では、PRAGMA FOREIGN_KEY_NAME を使用して外部キー制約の名前を取得できるようになりますが、まだ実験段階の機能です。
外部キー制約の名前は、スキーマの変更時に自動的に更新されないことに注意してください。手動で更新する必要があります。

-- サンプルデータベースを作成する
CREATE TABLE parent_table (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE child_table (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

-- 外部キー制約の名前を取得する
PRAGMA TABLE_INFO(child_table);

このコードを実行すると、以下の出力が得られます。

CID     NAME      TYPE        NOT NULL    DEFAULT  PRIMARY KEY    FOREIGN KEY
-------  ---------  -----------  ---------  --------  ------------  ------------
1       id         INTEGER     NOT NULL         PRIMARY KEY
2       parent_id  INTEGER     NOT NULL

FOREIGN KEY 列に child_table テーブルの外部キー制約の名前が表示されます。この例では、外部キー制約の名前は fk_child_parent です。

このコードはあくまでも例であり、実際の状況に合わせて変更する必要があります。例えば、複数の外部キー制約を持つテーブルの場合は、ループを使用してそれぞれの制約の名前を取得する必要があります。

SQLite における外部キー制約の名前を取得するその他の方法

GET_DDL ビューを使用する

SQLite 3.31.0 以降では、GET_DDL ビューを使用してテーブルのDDLを取得できます。DDLには、外部キー制約とその名前に関する情報が含まれています。

SELECT * FROM GET_DDL('child_table');

--
-- Table: child_table
--
CREATE TABLE child_table (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

出力結果には、FOREIGN KEY 制約の定義とその名前が含まれています。

SQLite マクロを使用する

SQLite には、SQLITE_HAS_COLUMN や SQLITE_GET_TABLE_COLUMN_NAME などのマクロが用意されています。これらのマクロを使用して、外部キー制約に関する情報を取得することができます。

-- サンプルマクロ
BEGIN
    -- 外部キー制約のカウントを取得する
    FOR EACH row IN (
        SELECT count(*) AS cnt
        FROM pragma_table_info('child_table')
        WHERE name LIKE '%FOREIGN KEY%'
    );
        -- 外部キー制約が存在する場合
        IF cnt > 0 THEN
            -- 外部キー制約の名前を取得する
            FOR EACH row IN (
                SELECT name
                FROM pragma_table_info('child_table')
                WHERE name LIKE '%FOREIGN KEY%'
            );
                -- 取得した名前を出力する
                RAISE INFO 'Foreign key constraint name: %s', row.name;
            END FOR;
        END IF;
    END FOR;
END;

このマクロを実行すると、child_table テーブルの外部キー制約の名前が表示されます。

サードパーティ製のライブラリを使用する

SQLite を操作するためのサードパーティ製のライブラリの中には、外部キー制約に関する情報を取得できるものがあります。例えば、Python の pysqlite3 ライブラリには、Pragma オブジェクトを使用して foreign_key_list 属性にアクセスする機能があります。

import sqlite3

conn = sqlite3.connect('database.db')
pragma = conn.pragma()

foreign_key_list = pragma.foreign_key_list(table_name='child_table')

for row in foreign_key_list:
    print(row[1])  # 外部キー制約の名前