Foreign Key Constraints in Oracle

Question

I have an Entity Relationship Model (ERD) where the IndividualCategory and TeamCategory refer to a Category object. 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 ensures that for each Individual_category will be a corresponding entry in the Category table of "super" (or "parent"?). There is only one IndividualCategory entry for a particular Category entry. Same for Team_category .

To ensure inheritance, I need one more restriction: a restriction that ensures that for each entry in the Category will be either an entry in IndividualCategory (X) or an entry in TeamCategory , but not both.

How to create such a restriction?

---

EDIT: This is what I meant by “inheritance in the ER model”. This is from my DB teacher slides (they call it "Entity sub-type" there, but sometimes call it just inheritance):

Answer 1

A completely different way to do this using deferred 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 Category is TeamCategory xor 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 deferred integrity constraints so that you can insert a category and a command / individual_category within the same transaction; otherwise, you could not insert a category in front of TeamCategory / IndividualCategory and vice versa. Trap-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 to do this 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] ) ); 

Then you can create views, for example:

 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 a view so that it looks like an application like a table.

Answer 3

Another way to implement complex constraints in a database is to use materialized representations (MVs).

In this example, MV can 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; 

Thus, MV is populated only for categories that violate the rule, but then the control restriction ensures that any transaction that leads to a row in MV will fail (since 1 = 0 will never be true).

In the past, I wrote about this approach here .

CAUTION: Although I am interested in this approach, I have never used it “in anger” in a production database. Careful benchmarking is necessary to ensure that the overhead of a full MV upgrade with every data change is not too high.

Answer 4

ERD inheritance is a classic example of a gen-spec design pattern. There are many articles on how to create gene specifications in relational DBMSs such as Oracle. you can find some of them by doing a Google search for “relational modeling of specialization generalizations.”

Most of what you see in these articles has already been outlined in other answers to this question. This topic popped up many times in SO. For an example of the previous discussion, click here .

The main feature of the classical solution is that specialized tables have an id column, which is both a primary key and a foreign key that refers to the id column of a generalized table. Thus, entities do not acquire their own personality. A feature that you really need to pay attention to is the limitation that implements the disjunction. Not all articles enforce this rule in their submitted decision.