Listing the Foreign Keys in a Firebird Database
We have just completed the code to reverse engineer a Firebird database and build the class definitions used by the Habanero framework
(No Ratings Yet)
Loading...6 comments
Leave a Reply
You must be logged in to post a comment.
That query is wrong. Here’s the correct one: Listing the Foreign Keys in a Firebird Database
Hi. We used your site extensively for the queries to get the tables, properties and primary keys, but we just couldn’t get the foreign key information out (the target table/field).
In the end I posted on Experts Exchange and realised I could get the info out of the INDICES and INDEX_SEGMENTS tables.
If you have a query that can obtain FK_NAME, SOURCE_TABLE, SOURCE_FIELD, TARGET_TABLE and TARGET_FIELD for us, then we’d appreciate it if you could post a response for our users on the above mentioned blog post.
Thanks!
It’s the “Detailed constraint info” query. Here’s the same query, limited to FOREIGN KEY constraints:
SELECT rc.RDB$CONSTRAINT_NAME AS constraint_name,
i.RDB$RELATION_NAME AS table_name,
s.RDB$FIELD_NAME AS field_name,
i.RDB$DESCRIPTION AS description,
rc.RDB$DEFERRABLE AS is_deferrable,
rc.RDB$INITIALLY_DEFERRED AS is_deferred,
refc.RDB$UPDATE_RULE AS on_update,
refc.RDB$DELETE_RULE AS on_delete,
refc.RDB$MATCH_OPTION AS match_type,
i2.RDB$RELATION_NAME AS references_table,
s2.RDB$FIELD_NAME AS references_field,
(s.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE rc.RDB$CONSTRAINT_TYPE = ‘FOREIGN KEY’
ORDER BY s.RDB$FIELD_POSITION
Go QUIPO!!!! If only they Googled …
🙁 looks like the Googled … but didn’t get it all
Thanks for that code. It doesn’t work right for composite keys. I changed the final join like this:
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME AND s.RDB$FIELD_POSITION = s2.RDB$FIELD_POSITION