How to get the names of foreign key constraints in SQLite?

Question

Does SQLite indeed have a limitation that it is not possible to retrieve the name of a foreign key? I am asking because I couldn't find this limitation mentioned anywhere in their documentation.

For example, I run the following script:

CREATE TABLE
       users (
       id INTEGER NOT NULL PRIMARY KEY,
       first_name TEXT NOT NULL,
       last_name TEXT NOT NULL
) ;

CREATE TABLE
       orders (
       id INTEGER NOT NULL PRIMARY KEY,       
       user_id INTEGER NOT NULL,       
       CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users(id)
) ;

Now I would like to check that the key "fk_users" was created indeed, so I run the following PRAGMA:

PRAGMA foreign_key_list(orders);

I would expect to see the name of my foreign key in the first column, but I am seeing some "0" value instead. Moreover, if I create multiple foreign keys with custom names, they are all called either "0" or "1".

Is this indeed a limitation of SQLite, or am I missing something?

Answer 1

There is no mechanism to extract the constraint name.

Answer 2

The table sqlite_master stores a CREATE command in the column "sql". You could query that command and do some parsing to extract the name of the foreign key. An example for a combined foreign key that works for me:

SELECT sql FROM sqlite_master WHERE name = 'song'

yields

CREATE TABLE "song" (
"songid"  INTEGER,
"songartist"  TEXT,
"songalbum"  TEXT,
"songname"  TEXT,
CONSTRAINT "fk__song_album" FOREIGN KEY ("songartist", "songalbum") REFERENCES "album" ("albumartist", "albumname")
)

and contains the name "fk__song_album" of the foreign key.

If one alters the foreign key with a query, the content of the sql column is modified/updated:

The text in the sqlite_master.sql column is a copy of the original CREATE statement text that created the object, except normalized as described above and as modified by subsequent ALTER TABLE statements. The sqlite_master.sql is NULL for the internal indexes that are automatically created by UNIQUE or PRIMARY KEY constraints.

https://www.sqlite.org/fileformat2.html

Extra tip: In order to see the foreign key information in Navicat (Lite) ... right click on a table and choose "Design table". Then select the foreign keys tab.