Thanks Svein,I can't make it work,I'm gonna create FK as a particular command in my code.




________________________________
From: Svein Erling Tysvaer <svein.erling.tysvaer@...>
To: firebird-support@yahoogroups.com
Sent: Wednesday, January 14, 2009 6:13:59 PM
Subject: Re: [firebird-support] Compound FOREIGN KEY and metadata extraction


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 !

[Non-text portions of this message have been removed]