Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Tue, 28 Jan 2014 21:37:34 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12691849.1390945006612.284.1390945054966@arcas>
In-Reply-To: <JIRA.12691849.1390945006612@arcas>
References: <JIRA.12691849.1390945006612@arcas>
Subject: [jira] [Created] (DERBY-6461) DatabaseMetaData.getExportedKeys()
 and getImportedKeys() incorrectly return information on UNIQUE constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Rick Hillegas created DERBY-6461:
------------------------------------

             Summary: DatabaseMetaData.getExportedKeys() and getImportedKeys() incorrectly return information on UNIQUE constraints
                 Key: DERBY-6461
                 URL: https://issues.apache.org/jira/browse/DERBY-6461
             Project: Derby
          Issue Type: Bug
          Components: JDBC
    Affects Versions: 10.11.0.0
            Reporter: Rick Hillegas


DatabaseMetaData.getExportedKeys() and getImportedKeys() are only supposed to return information on foreign keys which reference primary keys. But Derby also returns information on foreign keys which reference unique keys also. This can give rise to ambiguous results. The following script shows this behavior:

connect 'jdbc:derby:memory:db;create=true';

create table t1
(
    primaryColumn int not null,
    uniqueColumn  int not null,
    constraint t1_primary primary key( primaryColumn ),
    constraint t1_unique unique( uniqueColumn )
);

create table t2
(
    key1 int not null,
    key2  int not null,
    constraint t2_foreign1 foreign key( key1 ) references t1( primaryColumn ),
    constraint t2_foreign2 foreign key( key2 ) references t1( uniqueColumn )
);

call syscs_util.syscs_register_tool( 'databaseMetaData', true );

-- works correctly. just returns the primary key
select pk_name, column_name, key_seq
from table( getPrimaryKeys( null, 'APP', 'T1' ) ) s
order by pk_name, key_seq;

-- incorrect. returns info on foreign keys which reference unique keys. this creates ambiguous results.
select fktable_name, fkcolumn_name, pktable_name, pkcolumn_name, key_seq
from table( getExportedKeys( null, 'APP', 'T1' ) ) s
order by fktable_name, pktable_name, key_seq;

-- incorrect. returns info on foreign keys which reference unique keys. this creates ambiguous results.
select fktable_name, fkcolumn_name, pktable_name, pkcolumn_name, key_seq
from table( getImportedKeys( null, 'APP', 'T2' ) ) s
order by fktable_name, pktable_name, key_seq;


--
This message was sent by Atlassian JIRA
(v6.1.5#6160)