Thanks,but the same 4 records are shown :(




________________________________
From: Richard Wesley <hawkfish@...>
To: firebird-support@yahoogroups.com
Sent: Wednesday, January 14, 2009 12:47:44 AM
Subject: Re: [firebird-support] Compound FOREIGN KEY and metadata extraction

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_segments 1.rdb$field_ name) AS pkColumnName,
(rdb$index_segments 1.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_constrain ts1.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.trytable au.com/now. html






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