JDBC Metadata for Oracle: Foreign keys that reference unique keys not reported

despanos commented 3 years ago

Expected behavior

In the presence of a multi-column foreign key in Oracle DB and after using JDBC metadata reader of org.jooq.DSLContext#meta(), org.jooq.Table#getReferences() should return a List containing this foreign key.

Actual behavior

org.jooq.Table#getReferences()returns an empty List.

Steps to reproduce the problem

Assuming the following DDL is executed against an Oracle DB:

CREATE TABLE "table1"(
    "id" number(*,0) NOT NULL,
    "country" character varying(256),
    CONSTRAINT table1_id_country_key UNIQUE ("id", "country"),
    CONSTRAINT table1_pkey PRIMARY KEY ("id")
);
CREATE TABLE "table2"(
    "id" number(*,0) NOT NULL,
    "name" character varying(256),
    "city" number(*,0),
    CONSTRAINT table2_pkey PRIMARY KEY ("id"),
    CONSTRAINT table2_fkey FOREIGN KEY ("city", "name") REFERENCES "table1" ("id", "country"));

the read JDBC metadata for table2 returns an empty List for foreign keys via org.jooq.Table#getReferences(). Single-column foreign keys seem to be properly reported. More information can be found at MCVE.

Versions

jOOQ: 3.15.1
Java: 16
Database (include vendor): Oracle-18.4.0-XE
OS: Windows 10
JDBC Driver: com.oracle.database.jdbc:ojbc8

lukaseder commented 3 years ago

Thanks a lot for your report. It seems we can't do much about this from our side, without replacing the JDBC DatabaseMetaData query by our own (which is an option, of course)

Running this with JDBC directly:

System.out.println(ctx.fetch(connection.getMetaData().getImportedKeys(null, "TEST", "table2")));

Yields an empty result:

+-----------+-------------+------------+-------------+-----------+-------------+------------+-------------+-------+-----------+-----------+-------+-------+-------------+
|PKTABLE_CAT|PKTABLE_SCHEM|PKTABLE_NAME|PKCOLUMN_NAME|FKTABLE_CAT|FKTABLE_SCHEM|FKTABLE_NAME|FKCOLUMN_NAME|KEY_SEQ|UPDATE_RULE|DELETE_RULE|FK_NAME|PK_NAME|DEFERRABILITY|
+-----------+-------------+------------+-------------+-----------+-------------+------------+-------------+-------+-----------+-----------+-------+-------+-------------+

Because the underlying SQL query is wrong:

SELECT NULL AS pktable_cat,
       p.owner as pktable_schem,
       p.table_name as pktable_name,
       pc.column_name as pkcolumn_name,
       NULL as fktable_cat,
       f.owner as fktable_schem,
       f.table_name as fktable_name,
       fc.column_name as fkcolumn_name,
       fc.position as key_seq,
       NULL as update_rule,
       decode (f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1) as delete_rule,
       f.constraint_name as fk_name,
       p.constraint_name as pk_name,
       decode(f.deferrable,       'DEFERRABLE',5      ,'NOT DEFERRABLE',7      , 'DEFERRED', 6      ) deferrability 
      FROM all_cons_columns pc, all_constraints p,
      all_cons_columns fc, all_constraints f
WHERE 1 = 1
  AND f.table_name = 'table2'
  AND f.owner = 'TEST'
  AND f.constraint_type = 'R'
  AND p.owner = f.r_owner
  AND p.constraint_name = f.r_constraint_name
  AND p.constraint_type = 'P' -- This is the problem
  AND pc.owner = p.owner
  AND pc.constraint_name = p.constraint_name
  AND pc.table_name = p.table_name
  AND fc.owner = f.owner
  AND fc.constraint_name = f.constraint_name
  AND fc.table_name = f.table_name
  AND fc.position = pc.position
ORDER BY pktable_schem, pktable_name, key_seq

You can find this query if you have dictionary view grants:

select * 
from v$sql s
where module = 'JDBC Thin Client'
order by s.last_active_time desc;

So, the problem isn't whether you're using composite keys, but whether you're referencing unique keys or primary keys. It seems JDBC works correctly here. jOOQ doesn't share JDBC's opinion, so again, we could fix this by running our own query...

Or, you could design your table like this as a workaround:

CREATE TABLE "table1"(
    "id" number(*,0) NOT NULL,
    "country" character varying(256),
    CONSTRAINT table1_id_country_key PRIMARY KEY ("id", "country"),
    CONSTRAINT table1_pkey UNIQUE ("id")
);

Or normalise, perhaps? What's the reason you're not referencing the primary key, but the unique key instead? What's the point of the unique key?

despanos commented 3 years ago

Thank you for immediately investigating this.

I don't have to support this particular schema per se, it's just that I need to reliably identify foreign keys in any possible user-provided schema. Since SQL supports it, I imagine that such a foreign key constraint would not be that rare in real-world schemas.

For the moment, and for this particular vendor, I could work with JDBC directly then.

lukaseder commented 3 years ago

Since SQL supports it, I imagine that such a foreign key constraint would not be that rare in real-world schemas.

I'd say it's relatively rare in normalised schemas, because there's hardly a need for both a surrogate key and a candidate key, and the candidate key being referenced instead of the surrogate key. I feel it violates 2NF, but of course, if normalisation isn't a given, then yes, this could be seen in the wild.

For the moment, and for this particular vendor, I could work with JDBC directly then.

Well, the point is that it doesn't work because of JDBC being used behind the scenes. JDBC DatabaseMetaData doesn't produce foreign keys referencing unique keys as per the spec.

Our code generator can handle it, and so can jOOQ-meta, but that API is a bit harder to use. But you could take inspiration by the query produced by OracleDatabase.loadForeignKeys(). It's almost the same as the one I showed above, but instead of filtering AND p.constraint_type = 'P', you'd be filtering AND p.constraint_type = IN ('P', 'U')

jOOQ / jOOQ