Question: RI from one field in one child to two different parents.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BD - 03 Apr 2008 00:13 GMT

Hi, all.

I'm trying to understand a certain RI question.

I have 3 tables.

TABLE_A has a PK of A_1.
TABLE_B has a PK of B_1,A_1 (it's a composite key, with the second
field being a FK to TABLE_A).

TABLE_C is intended to have a value, such that it has RI to both
TABLE_A and TABLE_B.

TABLE_C would have a column of A_1, referencing TABLE_A, and two
columns which would be children to the composite primary key TABLE_B.

My question is - can I have a child value in TABLE_C, with relations
to both TABLE_A and TABLE_B, such that the value to be inserted in
TABLE_C.A_1 must exist in both TABLE_A and TABLE_B?

Strikes me that you can't do that... and that the RI from TABLE_C
should be to TABLE_B only - such that C would be a child to B, which
would be a child to A.

Any thoughts on that?

Thanks!

Reply to this Message

BD - 03 Apr 2008 01:02 GMT

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...

Reply to this Message

jefftyzzer - 03 Apr 2008 02:35 GMT

> Got some more clarification here.
>
[quoted text clipped - 17 lines]
>
> 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

Reply to this Message

BD - 03 Apr 2008 17:40 GMT

> > Got some more clarification here.
>
[quoted text clipped - 112 lines]
>
> - Show quoted text -

That looks very helpful - should well have just tried it myself, I
guess. ;-)

The only other thing I'd want to try (and may well do) would be to
establish what conditions would violate RI - presumably, if table B
was populated with (1,1), (2, 3), (3,6) and (4,8), and A was populated
with (1), (2) and (3), then an attempt to insert a value of (4, null)
into table C should fail, as the absence of a second value in the
insert would cause it to validate through the RI against table A,
whcih lacks a value '4'.

Or that's the goal, anyway.

Regardless - thanks for taking the time to lay that out! ;-)

Reply to this Message

jefftyzzer - 03 Apr 2008 18:05 GMT

> > > Got some more clarification here.
>
[quoted text clipped - 127 lines]
>
> Regardless - thanks for taking the time to lay that out! ;-)

The (4,8) insert into table B would fail too (FK violation because of
missing 4 in A). Also, in case you decide to experiment using the DDL
I gave here, I don't want you to get tripped-up by the tiny-but-
important detail that, in my scheme above, the order of the columns in
table B is (B1, A1).

--Jeff

Reply to this Message

--CELKO-- - 04 Apr 2008 00:19 GMT

It would really help if you would post DDL and not narrative
descriptions. It would also help if you used the proper terms --
referenced and referencing tables, not child and parent; columns and
not fields; etc. Let me make a guess at what you were trryignto
describe.

>> my question is - can I have a child [sic] value in table C, with relations to both table A and table B, such that the value to be inserted in C.a1 must exist in both table A and table B <<

CREATE TABLE A
(a1 INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE B
(a1 INTEGER NOT NULL UNIQUE -- required if it is to be referenced
REFERENCES A(a1),
b1 INTEGER NOT NULL,
PRIMARY KEY (a1, b1)); -- now made redundant by a1

CREATE TABLE C
(a1 INTEGER NOT NULL
REFERENCES A(a1),
a2 INTEGER NOT NULL
REFERENCES B(a1),
CHECK(a1 = a2),
b1 INTEGER NOT NULL,
FOREIGN KEY (a1, b1)
REFERENCES B(a1, b1));

This will do what you asked, but Ghod, its ugly!

Reply to this Message

BD - 04 Apr 2008 22:05 GMT

> It would really help if you would post DDL and not narrative
> descriptions.

Sure it would, but I wasn't even sure if the principle was sound
(which was all I was really asking) - kinda tough to create a script
for something that's not viable in principle.

> It would also help if you used the proper terms --
> referenced and referencing tables, not child and parent; columns and
> not fields; etc.

Fair enough.

> Let me make a guess at what you were trryignto
> describe.

Thanks for the feedback. I'll work with it a bit more.

Reply to this Message

--CELKO-- - 04 Apr 2008 22:11 GMT

>> kinda tough to create a script for something that's not viable in principle. <<

Nah! People write crappy code all the time :)

>> Thanks for the feedback. I'll work with it a bit more. <<

If you come up with something, please post it back here. FWIW, many
years ago Hunter Shu proposed a "pendant" constraint for SQL which was
something like this. The mental image was a data element "pendant"
suspended by many references and it could not be deleted or updated
until all the holders agreed.

Reply to this Message