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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users