Foreign Key Constraints in Oracle

Question

I have Entity Relationship Model (ERD) where entities IndividualCategory and TeamCategory relate to entity Category. Now I want to create tables in Oracle DB. I started like this:

CREATE TABLE Category(
    category_id INT PRIMARY KEY,
    ...
);

CREATE TABLE Individual_category(
    category_id INT CONSTRAINT fk_cat_indivcat REFERENCES Category(category_id),
    ...,
    CONSTRAINT pk_indivgamecat PRIMARY KEY (category_id)
);

CREATE TABLE Team_category(
    category_id INT CONSTRAINT fk_cat_teamcat REFERENCES Category(category_id),
    ...,
    CONSTRAINT pk_teamcat PRIMARY KEY (category_id)
);

This combination of Foreign key and Primary key constraints assures that for every Individual_category there will be corresponding record in Category "super" table (or "parent" table ?). And there will be only one IndividualCategory record for particular Category record. Same for Team_category.

To enforce inheritance I need one more constraint: A constraint that assures that for every record in Category there will be either record in IndividualCategory (X)OR a record in TeamCategory but not both.

How do I create such constraint ?

---

EDIT: This is what I meant by 'inheritance in E-R model'. This is from my DB teacher's slides (they call it "Entity sub-type" there but they sometimes call it just inheritance):

Answer 1

A completely different way to do this using deferrable constraints:

CREATE TABLE Category(
    category_id INT PRIMARY KEY,
    team_category_id INT,
    individual_category_id INT,
    ...
);

CREATE TABLE Individual_category(
    individual_category_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    ...,
);

CREATE TABLE Team_category(
    team_category_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    ...,
);

Make sure a Category is a TeamCategory xor an IndividualCategory:

alter table Category add constraint category_type_check check
  (   (team_category_id is null and individual_category_id is not null)
   or (team_category_id is not null and individual_category_id is null)
  );

Create deferrable integrity constraints so that one can insert a Category and Team/Individual_Category within the same transaction; otherwise, you couldn't insert a Category before the TeamCategory/IndividualCategory, and vice-versa. A catch-22.

alter table category add constraint category_team_fk 
  foreign key (team_category_id)
    references team_category (team_category_id) 
    deferrable initially deferred;

alter table category add constraint category_individual_fk 
  foreign key (individual_category_id)
    references individual_category (individual_category_id) 
    deferrable initially deferred;

alter table individual_category add constraint individual_category_fk
  foreign_key (category_id) 
  references category (category_id)
  deferrable initially deferred;

alter table team_category add constraint team_category_fk
  foreign_key (category_id) 
  references category (category_id)
  deferrable initially deferred;

Answer 2

How one may do this is, using a simplified example:

CREATE TABLE Category(
    category_id INT PRIMARY KEY,
    category_type varchar2(300) not null,
    ...
    [list of required attributes for only individual category, but nullable],
    [list of required attributes for only team category, but nullable]
);

alter table category add constraint check_category_individual check
  (   category_type <> 'INDIVIDUAL' 
   or (    category_type = 'INDIVIDUAL' 
       and [list of individual category attributes IS NOT NULL]
      )
  );

alter table category add constraint check_category_team check
  (   category_type <> 'TEAM' 
   or (    category_type = 'TEAM' 
       and [list of team category attributes IS NOT NULL]
      )
  );

You could then create views, like:

create view individual_category as
select category_id, [base category attributes], [individual category attributes]
  from category
 where category_type = 'INDIVIDUAL;

You can even put an INSTEAD OF trigger on the view so it would be appear to the application to be a table like any other.

Answer 3

Another way to implement complex constraints in the database is using materialized views (MVs).

For this example an MV could be defined as follows:

create materialized view bad_category_mv
refresh complete on commit 
as
select c.category_id
from category c
left outer join individual_category i on i.category_id = c.category_id
left outer join team_category i on t.category_id = c.category_id
where (  (i.category_id is null and t.category_id is null)
      or (i.category_id is not null and t.category_id is not null)
      );

alter table bad_category_mv
add constraint bad_category_mv_chk
check (1=0) deferrable;

So the MV is populated only for categories that break the rule, but then the check constraint ensures that any transaction that results in a row in the MV will fail (since 1=0 is never true).

I have blogged about this approach in the past here.

CAUTION: Although I am interested in this approach I have never used it "in anger" in a production database. Careful benchmarking is needed to ensure that the overhead of the full MV refresh whenever the data is changed is not too high.

Answer 4

ERD inheritance is a classic example of the gen-spec design pattern. There are numerous articles on how to design gen-spec in a relational DBMS like Oracle. you can find some of them by doing a Google search on "generalization specialization relational modeling".

Much of what you will see in these articles has already been outlined by other responses to this question. This topic has surfaced many times in SO. For a sample prior discussion, click here.

The main feature of the classic solution is that the specialized tables have an id column that is both a primary key and a foreign key that references the id column of the generalized table. In this manner, the subentities do not acquire an identity of their own. The feature you really need to watch out for is the constraint that implements disjunction. Not all of the articles enforce this rule in their presented solution.