Hi Mr. John!

This is way beyond my knowledge of the system tables, but from what I
can see from your query, you seem to be joining based on the index name
and constraint name. Expecting these to be equivalent for both fields,
I'm not surprised that both fields are joined together and that you end
up with double the amount of rows that you expect.

What happens if you add s.RDB$FIELD_POSITION = s2.RDB$FIELD_POSITION to
your latest LEFT JOIN or to the WHERE clause (if you add it to the WHERE
clause, then you actually are doing inner JOINs and could remove LEFT
from all your JOIN clauses, possibly excepting RDB$INDICES)? I'm just
guessing, I have no clue what RDB$FIELD_POSITION really contains, it
just sounds sensible.

HTH,
Set

Mr. John wrote:

> Hi !
> I have 2 tables
> table1(f1,f2...) with compound primary key PK(f1,f2)
> and
> table2(f1,f2...) with a FOREIGN KEY to table1 FK(f1,f2) created this way
>
> ALTER TABLE table2 ADD CONSTRAINT FK1 FOREIGN KEY (f1,f2) REFERENCES table2 (f1,f2);
>
> My application extracts metadata,compare it and generates missing fields/constraints,but this time extracting metadata for this table/foreign key shows 4 records,something like this :
>
> ..FIELD NAME REFERENCES FIELD
> f1 f2
> f1 f1
> f2 f2
> f2 f1
>
> I was exepecting for 2 records,something like this
> ...FIELD NAME REFERENCES FIELD
> f1 f1
> f2 f2
> what is wrong?
>
> this is my query for metadata
>
> SELECT rc.RDB$CONSTRAINT_NAME, s.RDB$FIELD_NAME AS field_name,
> rc.RDB$CONSTRAINT_TYPE AS constraint_type, 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
> i.RDB$RELATION_NAME= 'table2' -<table name
>
> I've got it from there http://www.alberton.info/firebird_sql_meta_info.html
>
> Thanks for any advice !