mercredi 27 avril 2016

SQLite: How to extract primary keys with correct names

Given a SQLite db.

a table with primary key:

create table t1 (id int not null, CONSTRAINT pk_id PRIMARY KEY (id));

Now query info for it:

PRAGMA TABLE_INFO(t1);

returns:
| cid | name | type | notnull | dflt_value | pk | | --- | ---- | ---- | ------- | -----------| -- | | 0 | id | int | 1 | <null> | 1 |

PRAGMA index_list(t1);

returns:

| seq | name | unique | origin | partial | | --- | ----------------------| ------ | ------ | ------- | | 0 | sqlite_autoindex_t1_1 | 1 | pk | 0 |

As we can see index_list returns info about the PK but it reports incorrect name ("sqlite_autoindex_t1_1" instead of "pk_t1").

Is it possible to extract real PRIMARY KEY name?

P.S. I can see that JetBrains's DataGrip correctly show PK names in database browser. But sqliteadmin for example shows sqlite_autoindex_

Aucun commentaire:

Enregistrer un commentaire