How to enforce a one-many relationship between a column in one table and a column in two parent tables?

Question

I have three tables in an existing database application. I don't have ERDs or other documentation about the table relations. There are no existing primary or foreign keys. I would like to model the relation and ideally enforce it with constraints.
The order table can have any number of orders for parts. The value of the Orders.part column must exist in either the Parts_inv table or the Parts_noninv table. I would think the original design should have had one parts table with a type column, but this what I have. Is there a way to model/enforce these relationships?

Orders

order     char(10)
part      char(20)

Parts_inv

part_num  char(50)

Parts_noninv

part_num  char(50)

This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings, names & line numbers & then read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Jul 20 '19 at 21:54
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jul 20 '19 at 21:57
Is PARTS_INV.PART_NUM UNION ALL PARTS_NONINV.PARTS_NUM unique? — Bob Jarvis - Слава Україні, Jul 20 '19 at 23:13
Thank you for the responses. I'm looking at the suggestions. To respond to questions asked: The parts are unique across both tables. Also, none of the parts in parts_inv or parts_noninv exceed 20 characters, so it might be possible to change the datatypes to match orders. — jon, Jul 20 '19 at 23:43

Bob Jarvis - Слава Україні · Answer 1 · 2019-07-21T02:27:18.557

Given just the tables you have you can't do this. But that's not to say it can't be done. :-)

Ideally you should change ORDER.PART to CHAR(50) to match the PARTS_NUM fields on the other tables - otherwise you won't be able to store all the possible PARTS_NUM on an ORDER. Perhaps there are business reasons for it being as it is, and perhaps someone was just clueless. No matter - try to fix it up if you can. It won't stop you completely, though.

Create another table - let's call it PARTS_ALL. It has two fields - PARTS_NUM which should match the data type of the PARTS_NUM fields on PARTS_INV and PARTS_NONINV, and SOURCE which should be VARCHAR2(1). Make the combination of (PARTS_NUM, SOURCE) the primary key. So it looks like

CREATE TABLE ALL_PARTS
  (PARTS_NUM   CHAR(50),
   SOURCE      VARCHAR2(1) NOT NULL,
   CONSTRAINT PK_ALL_PARTS
     PRIMARY KEY (PARTS_NUM, SOURCE));

You then copy the data from PARTS_INV and PARTS_NONINV into ALL_PARTS, adding the appropriate SOURCE value:

INSERT INTO ALL_PARTS (PARTS_NUM, SOURCE)
  SELECT PARTS_NUM, 'I' AS SOURCE FROM PARTS_INV
  UNION ALL
  SELECT PARTS_NUM, 'I' AS SOURCE FROM PARTS_NONINV

Now you define a couple of triggers on PARTS_INV and PARTS_NONINV to propagate inserts and deletes on those tables to ALL_PARTS:

CREATE TRIGGER PARTS_INV_AID
  AFTER INSERT OR DELETE ON PARTS_INV
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO ALL_PARTS (PARTS_NUM, SOURCE) VALUES (:NEW.PARTS_NUM, 'I');
  ELSIF DELETING THEN
    DELETE FROM ALL_PARTS WHERE PARTS_NUM = :OLD.PARTS_NUM;
  END IF;
END PARTS_INV_AID;
/

CREATE TRIGGER PARTS_NONINV_AID
  AFTER INSERT OR DELETE ON PARTS_NONINV
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO ALL_PARTS (PARTS_NUM, SOURCE) VALUES (:NEW.PARTS_NUM, 'N');
  ELSIF DELETING THEN
    DELETE FROM ALL_PARTS WHERE PARTS_NUM = :OLD.PARTS_NUM;
  END IF;
END PARTS_NONINV_AID;
/

Now your application suite can continue to INSERT and DELETE rows from PARTS_INV and PARTS_NONINV as they've always done, and those changes will be propagated to ALL_PARTS.

NOW (finally!) you can define your foreign key from ORDERS to ALL_PARTS and get the validation you wanted:

ALTER TABLE ORDER
  ADD CONSTRAINT ORDER_FK1
    FOREIGN KEY (PART) REFERENCES ALL_PARTS (PARTS_NUM);

Is this ideal? No. IDEALLY you'd get rid of PARTS_INV and PARTS_NONINV, and replace them with ALL_PARTS, modifying all your applications and web apps and back-office software to use the new table - so probably not gonna happen - but given the situation you find yourself in it may be about as good as you can do.

dbfiddle here

In my (admittedly non-professional) opinion this is bad advice. Obviously, you would also need to write trigger code for UPDATE operations. And you have to prevent DML on the newly created table. You don't have these issues with a materialized view, rather than a new table plus triggers. — , Jul 21 '19 at 01:06
@mathguy: I'd initially thought of using a materiaized view but I don't have a lot of experience with them so I'm not familiar with what can and can't be done with them. Can a primary or unique key be defined on a materialized view, and can a foreign key then be created on another table which references the MV? I tried playing with it briefly on dbfiddle.uk but found I couldn't create an MV there and so dropped the idea. I'd love to see a fully fleshed-out implementation using an MV, though. :-) — Bob Jarvis - Слава Україні, Jul 21 '19 at 02:30
OK, I just posted such a fully fleshed-out (but minimal) implementation. — , Jul 21 '19 at 05:53

score 1 · Answer 2 · answered Jul 21 '19 at 05:52

Here's one way to achieve this, using a materialized view.

In my full working example, I create two tables, T1 and T2. Each has an ID column, and another column that is not the same in the two tables (NAME in one, PRICE in the other, not even the same data type). The task is to create a primary key from the UNION ALL of columns ID from both tables.

First, ID must be PK in each table. Then we create a materialized view (MV) from the UNION ALL of the ID columns from the two tables, and declare the column ID in the MV as the primary key of the MV. Then, in the last step, I create a child table T, with a column ID, foreign key pointing to the MV (not to T1 or T2, but the union all of the ID's from both tables).

You may want to test various things that you could do with the three tables (T1, T2 and T) to see how this all works.

Two things to keep in mind: (1) PK and FK constraints on tables are usually checked at INSERT / UPDATE / DELETE time (if the constraints are "immediate"); this will be the case for the PK constraints on ID, separately for T1 and T2. However, the joint PK constraint on the MV is only checked at COMMIT time. The FK constraint on table T is checked "immediate" (after each INSERT, UPDATE or DELETE - or MERGE - statement). (2) Checking the PK constraint on the MV adds marginal overhead to DML statements on T1 and T2.

So, here goes:

create table t1 (id number primary key, name varchar2(100));
create table t2 (id number primary key, price number not null);

create materialized view log on t1 with primary key, rowid including new values;
create materialized view log on t2 with primary key, rowid including new values;

create materialized view t12 (id, rid, source)
refresh fast on commit
as
select id, rowid, 't1' from t1 union all select id, rowid, 't2' from t2
;

alter materialized view t12 add constraint t12_pk primary key (id);

create table t (id number references t12, eff_date date not null);

score 0 · Answer 3 · answered Jul 20 '19 at 21:52

0

Unfortunately not.

You can define two foreign key constraints, but they will both need to be valid for every row in the table.

The second problem is the choice of char datatype. No char(10) value in ORDERS will match a char(50) in either parent table. Probably the person who chose the data type was not familiar with it.

answered Jul 20 '19 at 21:52

William Robertson

15,273
4
38
44

Regarding comparing CHAR(10) (or (20) or (whatever)) to CHAR(50) - I thought so too, but after creating [this dbfiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=92c5ca063d26a7c71fbdb8e86b50e990) I discovered I was mistaken. Clearly, values can be stored in the longer field which can't be stored in the shorter field, but when the non-trailing-blank portion is "the same" they appear to compare as equal. – Bob Jarvis - Слава Україні Jul 21 '19 at 02:41
@BobJarvis - That is because, as documented, two CHAR values compare as equal if the longer value equals the shorter value, right-padded with spaces. We may not like this definition, but that's what Oracle says. – Jul 21 '19 at 05:56
It seems the rules for joining `CHAR(20)` to `CHAR(50)` are not the same as the rules for enforcing FK constraints, unless I am missing something (entirely possible as I never use `CHAR`). [dbfiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6cab8f483cbefb00d56f3c09c42f2a4c) – William Robertson Jul 21 '19 at 09:33

score 0 · Answer 4 · answered Jul 21 '19 at 11:55

You can also create an insert and update Trigger on the ORDERS table to fulfill the requirement.

As per your comment, PART_NUM will be unique across two tables. So you can create the trigger as follows:

CREATE OR REPLACE TRIGGER ORDERS_TRG BEFORE
    INSERT OR UPDATE ON ORDERS
    FOR EACH ROW
DECLARE
    LV_COUNT   NUMBER;
BEGIN
    SELECT
        1
    INTO LV_COUNT
    FROM
        DUAL
    WHERE
        :NEW.PART IN (
            SELECT
                PART_NUM
            FROM
                PARTS_INV
            UNION ALL
            SELECT
                PART_NUM
            FROM
                PARTS_NONINV
        );

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE;
END ORDERS_TRG;
/

db<>fiddle demo

Update the exception-handling part according to your requirement.

Cheers!!

How to enforce a one-many relationship between a column in one table and a column in two parent tables?

Orders

Parts_inv

Parts_noninv

4 Answers4