0

If I have a parent table in Oracle, with two children, I know that I can enforce a FK so that the parent exists.

Is there a way to ensure that a child record only exists in one of the children, but not both?

In other words, disallow the insert into child1 if there is already a record with the same id in child1?

CREATE TABLE parent (
  id         VARCHAR2(10)  NOT NULL PRIMARY KEY,
  some_date  VARCHAR2(10)
);


CREATE TABLE child1 (
  id         VARCHAR2(10)  NOT NULL PRIMARY KEY,
  some_date  VARCHAR2(10),
  FOREIGN KEY (id) REFERENCES parent(id)
);


CREATE TABLE child2 (
  id         VARCHAR2(10)  NOT NULL PRIMARY KEY,
  some_date  VARCHAR2(10),
  FOREIGN KEY (id) REFERENCES parent(id)
);
EdgeCase
  • 4,719
  • 16
  • 45
  • 73
  • You'd have to write a trigger on both children to do the check on insert. – StevieG Sep 18 '13 at 13:53
  • "disallow the insert into child1 if there is already a record with the same id in child1" You already implemented this check by having primary key. ;) – Srini V Sep 18 '13 at 13:54
  • 3
    Create only one child table, and differentiate childs with a column. – PepeDeLew Sep 18 '13 at 13:55
  • See the answer by @Erwin in this question: [Complex foreign key constraint in SQLAlchemy](http://stackoverflow.com/questions/8394177/complex-foreign-key-constraint-in-sqlalchemy/8395021#8395021) that uses `DEFERRABLE` foreign key constraints (under the **All key columns NOT NULL** title). – ypercubeᵀᴹ Sep 18 '13 at 14:12
  • Another almost identical question and answer: [Implementing a “Both, Either-or, but Not Null” Requirement in a Database](http://stackoverflow.com/questions/10456525/implementing-a-both-either-or-but-not-null-requirement-in-a-database/10468475#10468475) (for Oracle this time) – ypercubeᵀᴹ Sep 18 '13 at 14:15

0 Answers0