Check and get Parent Tables - Firebird
Anyone can help me a bit?
I use this Query to get all the constraint names, foreign tables, fields with their contraint tables and fields...
select distinct Con.rdb$constraint_name, Rel.rdb$relation_name TableName ,Rel.rdb$field_name FieldName, FCon.rdb$Relation_Name ForeignTableName, FIseg.rdb$Field_Name ForeignFieldName from rdb$relation_fields Rel inner join rdb$relation_constraints Con on (Con.rdb$relation_name = Rel.rdb$relation_name and Con.rdb$constraint_type like 'FOREIGN%') inner join rdb$indices IDX on (IDX.rdb$index_name = Con.rdb$index_name) inner join rdb$index_segments ISeg on (ISeg.rdb$index_name = Idx.rdb$index_name and ISeg.rdb$Field_Name = Rel.rdb$field_name) inner join rdb$Relation_Constraints FCon on (FCon.rdb$index_name = Idx.rdb$Foreign_Key) inner join rdb$index_segments FIseg on (FISeg.rdb$index_name = Idx.rdb$Foreign_key and FISeg.rdb$Field_Position = ISeg.rdb$Field_Position) where Rel.rdb$relation_name not like 'RDB$%' and FCon.rdb$Relation_Name <> Rel.rdb$relation_name and FCon.rdb$Relation_Name = :TABLENAME
I would like to check whether the specified table (:TABLENAME) has a parent table or not and I need it's name, foreignfieldname etc like in my first query.
Applications -> Licenses -> Licenseinfos -> '' -> Registrations -> ''
If I add Licenseinfos I would like to get "Licenses" If I add Registrations I would like to get "Licenses"
Thanks for the answers! I use firebird 2.5
The simplest way to do this is using the following query. It identifies both sides of the constraint using the table RDB$REF_CONSTRAINTS, this doesn't need to do anything with the RDB$INDICES table (which would be a complication if you had keys with multiple columns):
select PK.RDB$RELATION_NAME as PKTABLE_NAME ,FK.RDB$RELATION_NAME as FKTABLE_NAME from RDB$RELATION_CONSTRAINTS FK inner join RDB$REF_CONSTRAINTS RC on FK.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME inner join RDB$RELATION_CONSTRAINTS PK on PK.RDB$CONSTRAINT_NAME = RC.RDB$CONST_NAME_UQ where FK.RDB$RELATION_NAME = :TABLENAME