Re: Datamodeling - Generic Arc Design

From: Jared Still <jkstill_at_bcbso.com>
Date: Mon, 30 Oct 2000 14:20:54 -0800 (PST)
Message-Id: <10665.120619@fatcity.com>

On Mon, 30 Oct 2000, Jeff Cox wrote:

> I am creating a Table Instance Chart from an E-R diagram and was wondering
> if there is a "rule of thumb" on how to reference the foreign key for my
> Arc.
>
> I need to create my foreign key reference for Table A which MUST pull the
> primary key from EITHER Table B or Table C. This foreign key is also part
> of the primary key for Table A.
>
>
> Jeff Cox
>

There are at least a couple of ways to do this.

---

       |                |
       |                |
       |      A         |
       |                |
       |                |
       ------------------
        \|/         \|/
         |           |
       \_|___________|_/
         |           |
         |           |
         |           | 
-------------     -------------
|           |     |           |
|           |     |           |
|    B      |     |    C      |
|           |     |           |
|           |     |           |
-------------     -------------

My preferred method is with FK's and a check constraint.

Another method is with discriminator column in table a that lets you know which table to look in, and a trigger to enforce integrity.

Here's some test code for the first method.

Jared

---

• test building and arc with 2 fk

drop table a cascade constraints;
drop table b cascade constraints;
drop table c cascade constraints;

create table b ( pk integer );
alter table b add constraint bpk primary key(pk);

create table c ( pk integer );
alter table c add constraint cpk primary key(pk);

create table a ( pk integer, b_pk integer, c_pk integer );
alter table a add constraint b_fk foreign key(b_pk) references b(pk);
alter table a add constraint c_fk foreign key(c_pk) references c(pk);

alter table a add constraint a_arc check ( not ( b_pk is not null and c_pk is not null ) );

insert into b values(1);
insert into c values(1);

commit;

insert into a(pk, b_pk) values(1,1);
insert into a(pk, c_pk) values(2,1);
insert into a(pk) values(3);

commit;

prompt This insert tries to assign both FK columns and should fail Received on Mon Oct 30 2000 - 16:20:54 CST