[hibernate-issues] [Hibernate-JIRA] Created: (HBX-1186) Missing many-to-one relationships in which foreign key references a unique key in the one side

[Rui Baeta (JIRA)]

Missing many-to-one relationships in which foreign key references a unique key in the one side
----------------------------------------------------------------------------------------------

                 Key: HBX-1186
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HBX-1186
             Project: Hibernate Tools
          Issue Type: Bug
          Components: reverse-engineer
    Affects Versions: 3.2.4.GA
         Environment: jdk1.5.0_11
hibernate-tools-3.2.4.GA
hibernate-core-3.3.2.GA
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
Oracle JDBC driver, version: 11.2.0.1.0
            Reporter: Rui Baeta
         Attachments: OracleMetaDataDialect.patch

In the reverse engineering process, Hibernate Tools is missing many-to-one relationships in which foreign key references a _unique key_ in the one side.

Here is a test case: table {{detail}} has a foreign key to table {{master}}, and references a column that has a unique constraint ({{uk_master}}).

{code:SQL} 
create table master (
    pk_master number(24), --primary key
    uk_master number(24), --unique key
    primary key (pk_master),
    unique (uk_master)
);

create table detail (
    pk_detail number(24), --primary key
    fk_master number(24), --foreign key
    primary key (pk_detail) 
);

alter table detail add constraint cns_fk_master
    foreign key (fk_master) references master(uk_master)
;
{code} 

Now, when executing Hibernate Tools with {{OracleMetaDataDialect}}, this relationship is not captured by the metadata query issued by {{OracleMetaDataDialect}} (constant {{SQL_FK_BASE}}). This is so because {{OracleMetaDataDialect}} is restricting foreign key relationships only to _primary key constraints_:
{code:SQL}constraint_type = 'P'{code}

If we change this restriction so that it includes also _unique key constraints_, it works:
{code:SQL}constraint_type in ('P','U'){code}

As far as I know, this change doesn't have side effects.

There is a patch attached.

(If you need, I can zip a simple maven project with this test case.)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira