On Apr 2, 5:02 pm, BD <robert.d...@gmail.comwrote:
Got some more clarification here.
TABLE_A has a PK of A_1.
TABLE_B has a PK of B_1, A_1, with A_1 being a FK to TABLE_A.
Clear as mud? Okay...
The request I'm looking at is for TABLE_C to have columns of B_1 and
A_1 as well, with RI against both TABLE_A and TABLE_B, such that:
If an insert into TABLE_C contains both B_1 and A_1, the RI will
reference TABLE_B.
If an insert into TABLE_C contains ONLY A_1 (B_1 is null), then the RI
will reference TABLE_A.
I don't know of a way to implement that kind of selective logic in RI.
I'm guessing it's not possible.
Anyone disagree?
Thanks for any insights...
While I don't necessarily endorse this approach--I'll leave the
lecturing to the denizens of c.d.t.--I think it could work:
create table jtyzzer.a (a1 int not null);
create table jtyzzer.b (a1 int not null, b1 int not null);
create table jtyzzer.c (a1 int, b1 int);
alter table jtyzzer.a add constraint a_pk primary key(a1);
alter table jtyzzer.b add constraint b_pk primary key(b1, a1);
alter table jtyzzer.b add constraint b_uc1 unique (a1);
alter table jtyzzer.b add constraint b_fk1 foreign key (a1) references
jtyzzer.a(a1);
alter table jtyzzer.c add constraint c_fk1 foreign key (a1) references
jtyzzer.a(a1);
alter table jtyzzer.c add constraint c_fk2 foreign key (b1, a1)
references jtyzzer.b(b1, a1);
insert into jtyzzer.a (a1) values (1);
insert into jtyzzer.a (a1) values (2);
insert into jtyzzer.a (a1) values (3);
insert into jtyzzer.a (a1) values (4);
insert into jtyzzer.b (b1, a1) values (1,1);
insert into jtyzzer.b (b1, a1) values (2,3);
insert into jtyzzer.c (b1, a1) values (1,1);
insert into jtyzzer.c (a1) values (2);
insert into jtyzzer.c (b1, a1) values (2, 3);
insert into jtyzzer.c (a1) values (4);
create table jtyzzer.a (a1 int not null)
DB20000I The SQL command completed successfully.
create table jtyzzer.b (a1 int not null, b1 int not null)
DB20000I The SQL command completed successfully.
create table jtyzzer.c (a1 int, b1 int)
DB20000I The SQL command completed successfully.
alter table jtyzzer.a add constraint a_pk primary key(a1)
DB20000I The SQL command completed successfully.
alter table jtyzzer.b add constraint b_pk primary key(b1, a1)
DB20000I The SQL command completed successfully.
alter table jtyzzer.b add constraint b_uc1 unique (a1)
DB20000I The SQL command completed successfully.
alter table jtyzzer.b add constraint b_fk1 foreign key (a1) references
jtyzzer.a(a1)
DB20000I The SQL command completed successfully.
alter table jtyzzer.c add constraint c_fk1 foreign key (a1) references
jtyzzer.a(a1)
DB20000I The SQL command completed successfully.
alter table jtyzzer.c add constraint c_fk2 foreign key (b1, a1)
references jtyzzer.b(b1, a1)
DB20000I The SQL command completed successfully.
insert into jtyzzer.a (a1) values (1)
DB20000I The SQL command completed successfully.
insert into jtyzzer.a (a1) values (2)
DB20000I The SQL command completed successfully.
insert into jtyzzer.a (a1) values (3)
DB20000I The SQL command completed successfully.
insert into jtyzzer.a (a1) values (4)
DB20000I The SQL command completed successfully.
insert into jtyzzer.b (b1, a1) values (1,1)
DB20000I The SQL command completed successfully.
insert into jtyzzer.b (b1, a1) values (2,3)
DB20000I The SQL command completed successfully.
insert into jtyzzer.c (b1, a1) values (1,1)
DB20000I The SQL command completed successfully.
insert into jtyzzer.c (a1) values (2)
DB20000I The SQL command completed successfully.
insert into jtyzzer.c (b1, a1) values (2, 3)
DB20000I The SQL command completed successfully.
insert into jtyzzer.c (a1) values (4)
DB20000I The SQL command completed successfully.
HTH, and let me know if I missed something.
--Jeff