Relationship between Unique Keys and Foreign Key

[KhemrajShankarappa]

Relationship between Unique Keys and Foreign Key

[KhemrajShankarappa]

Can someone pls help

[npetzall]

Could you supply a small example with just two tables and how the relationship is declared.

Implied won't find it I think, it only looks at PK.

But we do ask the jdbc driver for imported and exported columns/keys.

For me to look into this I also need to know the version of Oracle and driver so that I'm sure to reproduce the issue.

Then I can check different driver if they allow this else we might consider expanding the implied to check on both PK and UQ indexes.

[KhemrajShankarappa]

Please see attached screenshot, Attendance_time table has FK to Attendance table (referring to UK for that table)

In schema spy this is not visible, hence its not showing up in relationships diagram . We are using ojdbc8-19.14.0.0 driver
Is there any fix for this? so that my FK on UK also visible

[KhemrajShankarappa]

Version of Oracle is 19.14

[npetzall]

Would you be able to supply Sql statement to produce this kind of relationship.

I'm gonna check if there is any filtering applied if a FK is returned but it doesn't reference a PK. Because

[KhemrajShankarappa]

Please use this test case:

CREATE TABLE SUPPLIER_KHEM
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE PRODUCTS_KHEM
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier_khem(supplier_id),
CONSTRAINT products_unique UNIQUE (product_id)
);

CREATE TABLE ORDERS_KHEM
( order_id numeric(10) not null,
prodids numeric(10) not null,
CONSTRAINT fk_orders
FOREIGN KEY (prodids)
REFERENCES products_khem(product_id)
);

You will see relation between supplier and products but Orders table is not in picture.

[npetzall]

I'll need some additional time to look into this, since I find the Oracle 19 container images for M1 a little sketchy since I can't find a release to download of the software for M1(arm64). I'll need to have time to sitt at a different computer.

[KhemrajShankarappa]

Sure, meanwhile i tried -meta option as a workaround. Its working but we cant use this method.