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.

For example:

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

Answers


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

Need Your Help

LINQ - Dynamic expression to OrderBy

asp.net-mvc linq entity-framework

I have a little issue, I'll try to explain this in detail.