[MonetDB-users] how to find the foreign key relationships
Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL? I now have: "select idxs.name, tables.name from sys.tables, sys.idxs where tables.id = idxs.table_id and idxs.type=1;" But that doesn't give me the related table. Any suggestion? I can't find documentation of the sys tables on the website, does it exist? Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
On Thu, 14 Jun 2012, Molenaar, Gijs wrote:
Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL?
If you look at; select * from _tables; You can also see a keys, does that help? Stefan ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ !DSPAM:1,4fd9b2f392541414512499! _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users !DSPAM:1,4fd9b2f392541414512499!
that's stupid of me, didn't see that one somehow. -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/ On Jun 14, 2012, at 12:04 PM, Stefan de Konink wrote: On Thu, 14 Jun 2012, Molenaar, Gijs wrote: Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL? If you look at; select * from _tables; You can also see a keys, does that help? Stefan------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ !DSPAM:1,4fd9b2f392541414512499! _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.netmailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users !DSPAM:1,4fd9b2f392541414512499! ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/___________________... MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
And how should I interpret the rkey value? I can't find a matching column with that ID? Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/ On Jun 14, 2012, at 12:04 PM, Stefan de Konink wrote: On Thu, 14 Jun 2012, Molenaar, Gijs wrote: Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL? If you look at; select * from _tables; You can also see a keys, does that help? Stefan------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ !DSPAM:1,4fd9b2f392541414512499! _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.netmailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users !DSPAM:1,4fd9b2f392541414512499! ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/___________________... MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
I have the same problem for finding out which field is the primary key. I now have: select k.name as key, t.name as tablename from sys.keys as k, sys.tables as t where k.type=0 and k.table_id = t.id; Should I stringparse the key field for finding out the column name? -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/ On Jun 14, 2012, at 12:16 PM, Molenaar, Gijs wrote: And how should I interpret the rkey value? I can't find a matching column with that ID? Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/ On Jun 14, 2012, at 12:04 PM, Stefan de Konink wrote: On Thu, 14 Jun 2012, Molenaar, Gijs wrote: Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL? If you look at; select * from _tables; You can also see a keys, does that help? Stefan------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ !DSPAM:1,4fd9b2f392541414512499! _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.netmailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users !DSPAM:1,4fd9b2f392541414512499! ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/___________________... MonetDB-users mailing list MonetDB-users@lists.sourceforge.netmailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/___________________... MonetDB-users mailing list MonetDB-users@lists.sourceforge.netmailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Gijs, On 14-06-2012 10:27:13 +0000, Molenaar, Gijs wrote:
I have the same problem for finding out which field is the primary key. I now have:
select k.name as key, t.name as tablename from sys.keys as k, sys.tables as t where k.type=0 and k.table_id = t.id;
Should I stringparse the key field for finding out the column name?
Perhaps you can "borrow" some code from JDBC and clients/mapiclient/dump.c Fabian
Hi Fabian, Thanks this has been incredibly useful. Djonet can now also do database introspection and reverse engineer the database schema for Django. -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/ On Jun 14, 2012, at 12:32 PM, Fabian Groffen wrote: Gijs, On 14-06-2012 10:27:13 +0000, Molenaar, Gijs wrote: I have the same problem for finding out which field is the primary key. I now have: select k.name as key, t.name as tablename from sys.keys as k, sys.tables as t where k.type=0 and k.table_id = t.id; Should I stringparse the key field for finding out the column name? Perhaps you can "borrow" some code from JDBC and clients/mapiclient/dump.c Fabian ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/___________________... MonetDB-users mailing list MonetDB-users@lists.sourceforge.netmailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Thu, 14 Jun 2012, Molenaar, Gijs wrote:
Should I stringparse the key field for finding out the column name?
I guess you have figured out that rkey (of a foreign key) points to a primary key in the table "keys"? No answer to your question above though... Stefan ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ !DSPAM:1,4fd9b9ee94817602690749! _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users !DSPAM:1,4fd9b9ee94817602690749!
participants (3)
-
Fabian Groffen -
Molenaar, Gijs -
Stefan de Konink