Hi,
I'm a complete sql novice so please go easy :)
I'm trying to create two queries so I can determine which of the keys
for a given sqlite3 table is the primary key and which keys are
foreign.
I understand that sqlite's pragmas aren't queryable so I have some
code that moves the result set from PRAGMA index_list(tablename) to a
table called pragma_index, and the result set from PRAGMA
foreign_key_list(tablename) into a table called pragma_fk.
Unfortunately I've come up against a brick wall because I can't see
the relationship between the two result sets that will tell me what I
want to find out. Double unfortunately, the foreign_key_list pragma
returns a result that that does not actually include the names of the
foreign keys so I'm completely *boggled* by that.
Table pragma_index looks like this:
seq INTEGER
name TEXT
unique INTEGER
Table pragma_fk looks like this:
id INTEGER
seq INTEGER
table TEXT
from TEXT
to TEXT
on_update TEXT
on_delete TEXT
match TEXT
Am I missing some table metadata in trying to do this? If so, what
else do I need?
I know I can interrogate sqlite_master and parse the sql that created
the table but this assumes
I'm sure the queries I need will look something like this, but I'm not
even sure that seq is the right field, if it should be pragma_fk.id,
or what:
SELECT name FROM pragma_index LEFT JOIN pragma_fk ON pragma_index.seq
= pragma_fk.seq WHERE pragma_fk.seq IS NOT NULL;
and...
SELECT name FROM pragma_index LEFT JOIN pragma_fk ON pragma_index.seq
= pragma_fk.seq WHERE pragma_fk.seq IS NULL;
Any help with this is greatly appreciated. Thanks for reading.
Kad.
PS: My sqlite is v3.7.13 and is running on Linux.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
