Oracle-DB: Ensure uniqueness across table boundaries
Consider the following requirement: There are two tables. Table MASTER is referenced from table DETAIL. CREATE TABLE Master ( ID NUMBER PRIMARY KEY , Company_ID NUMBER ); CREATE TABLE Detail ( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, Master_ID NUMBER REFERENCES Master(ID), Value NUMBER ); The content of the table DETAIL should be unique in a combination of two values: the column DETAIL.VALUE and the column MASTER.COMPANY_ID from a directly referenced table. Each column of this tables except the primary key columns can be changed. Limits of solution approach with function based index A first thought when securing uniqueness across table boundaries is often the use of function based indexes with a function that selects the second value from the referenced table. CREATE OR REPLACE FUNCTION Get_Company_ID(p_Master_ID IN NUMBER) RETURN NUMBER DETERMINISTIC IS v_Company_ID NUMBER; BEGIN SELECT Company_ID INTO v_Comp