Is there a way to order the result of a sqlite PRAGMA foreign_key_list() query by the order, the columns in the table the query is taken on, appear?

Question

I want to programmatically add an index to a sqlite table if it has more lines than a set limit size. I want to add the index to all columns which have a foreign key, to get those, I use

PRAGMA foreign_key_list(myTable)

where I use the from column to get the columns from my table which have a foreign key. Since the order of a index with several columns is important I want to order them in the order they appear in the table. Is there a way to find the order within this PRAGMAquery (and how), or do I have to make another query (PRAGMA table_info(myTable)) and find the order programmatically?

EDIT: working with .db3 files.

Answer 1

It would appear, with limited testing undertaken, that if the foreign keys are defined at the column level, then the id column is a higher value for the earlier defined foreign keys.

You can use pragma's that return results in SELECT statements such as and sort according to one of the columns (id has been used if a suitable column naming convention were used the you could sort according to the from column (noting that it needs to be enclosed))

SELECT * FROM pragma_foreign_key_list('mytable') ORDER BY id ASC;

For example consider the following :-

DROP TABLE IF EXISTS mytable;


CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY,
  _otherd_building_id_reference INTEGER REFERENCES _building(_id),
    _building_id_reference INTEGER REFERENCES _building(_id),
    _room_id_reference INTEGER REFERENCES _room(_id),
    _campus_id_reference INTEGER REFERENCES _campus(_id),
    _othera_campus_reference INTEGER REFERENCES _campus(_id),
    _otherz_room_id_reference INTEGER REFERENCES _room(_id),
    _otherc_building_id_reference INTEGER REFERENCES _building(_id)
    );


SELECT * FROM pragma_foreign_key_list('mytable') ORDER BY id DESC;

• Note that initially columns without a prefix like *other? were added, othera,c and z were then added at the end, and then finally _otherd_building_id_reference was added.

The result, when running the above is consistently :-