On 13 Jan 2009, at 11:52, Mr. John wrote:

> 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?

Dunno, but here is the one we use:

SELECT
(rdb$index_segments.rdb$field_name) AS fkColumnName,
(rdb$index_segments1.rdb$field_name) AS pkColumnName,
(rdb$index_segments1.rdb$field_position) AS pkOrdinal,
(rdb$relation_constraints1.rdb$relation_name) AS pkTableName,
(rdb$relation_constraints.rdb$constraint_name) AS constraintName
FROM rdb$relation_constraints
INNER JOIN rdb$index_segments ON (rdb$relation_constraints.rdb
$index_name = rdb$index_segments.rdb$index_name)
INNER JOIN rdb$indices ON (rdb$relation_constraints.rdb
$index_name = rdb$indices.rdb$index_name)
INNER JOIN rdb$relation_constraints rdb$relation_constraints1 ON
(rdb$indices.rdb$foreign_key = rdb$relation_constraints1.rdb$index_name)
INNER JOIN rdb$index_segments rdb$index_segments1 ON (rdb
$relation_constraints1.rdb$index_name = rdb$index_segments1.rdb
$index_name)
WHERE rdb$relation_constraints.rdb$relation_name = '<table name>';

Let me know if it works ;-)
________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html