CODEWITHSUNDEEP
c#sqlite
Roland Deschain
Roland Deschain

Reputation: 2830

Is it possible to query all columns within a table that have references to other tables in sqlite?

Pretty much as the title says, if I have a table, created like this:

CREATE TABLE "MyTable" ( 
"ID" INTEGER PRIMARY KEY NOT NULL, 
"OtherTable1ID" INTEGER NOT NULL CONSTRAINT 'OtherTable1ID' REFERENCES 'OtherTable1' ('ID'), 
"OtherTable2ID" INTEGER NOT NULL CONSTRAINT 'OtherTable2ID' REFERENCES 'OtherTable2' ('ID'), 
"Repetition" INTEGER
)

I'm looking for a query, that returns me the column names OtherTable1IDand OtherTable2ID.

My own approach would be to query the table's create statement and look for the REFERENCES keyword (with Regex), but I was hoping there is a more simple solution?

Second approach is to get all column names within the table, and look if they contain 'ID' in the name. This works mostly, since the databases in this case all follow this naming convention, however what if a table holds a column which name contains 'ID' but doesn't reference anything? Therefore I need a generally applicable solution.

Upvotes: 0

Views: 65

Answers (1)

Shawn
Shawn

Reputation: 52344

You can list and get information about a table's foreign key constraints with the foreign_key_list pragma:

PRAGMA foreign_key_list(MyTable);

If you just want the names of columns and don't care about the rest of the information (or want to use them in a bigger query):

SELECT "from" FROM pragma_foreign_key_list('MyTable');

Upvotes: 1

Related Questions