Foreign Key Constraints in Oracle

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 ER model'. This is from my DB teacher's slides (they call it "Entity sub-type" there but they sometimes call it just inheritance):

---

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;

---

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.

---

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.

链接地址: http://www.djcxy.com/p/65256.html

上一篇: Datomic中的数据建模

下一篇: Oracle中的外键约束