473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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

BD
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!

Apr 2 '08 #1
6 1255
BD
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...
Apr 3 '08 #2
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
Apr 3 '08 #3
BD
On Apr 2, 6:35*pm, jefftyzzer <jefftyz...@sbcglobal.netwrote:
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- Hide quoted text -

- 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! ;-)
Apr 3 '08 #4
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!

Apr 3 '08 #5
BD
On Apr 3, 4:19*pm, --CELKO-- <jcelko...@earthlink.netwrote:
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.
Apr 4 '08 #6
>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.

Apr 4 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Doug Estep | last post by:
Below is a sample of my XML… <MetaData> <Table name="mytable"> <PrimaryKey name="pk"><Column name="mycolumn"/></PrimaryKey> </Table> </MetaData> <Sequencer> <Table name="mytable">
5
by: eric | last post by:
Hi,everyone: I have my xml data defined as xml data definition I: <?xml version="1.0"?> <parents> <parent> <id>1000</id>
16
by: Suzanne Vogel | last post by:
Hi, I've been trying to write a function to test whether one class is derived from another class. I am given only id's of the two classes. Therefore, direct use of template methods is not an...
4
by: Abbey Krystowiak | last post by:
Does anyone know if I can have a field in a row where I can add two lines of info without adding a whole new line? and then there would be a drop down menu? *** Sent via Developersdex...
4
by: james | last post by:
I have a custom UserControl, which can have many sub class levels derived from it. I want to be able to discover all the components at Load time, but the only components I can see from the base...
3
by: Chris | last post by:
Before I started to create table, etc to track unique form field record number assigments I thought I'd check to see if there is now a better way to do this in .NET. I have a parent form (table)...
4
by: Bob | last post by:
While testing my my program I came up with a consistency exception. My program consists of three datagridviews, One called dgvPostes which is the parent grid and its two children,one called...
5
by: usenet | last post by:
Hi. I've been struggling with a query, so I'm hoping that I can explain it in understandable terms. Given the tables PARENT (id, foo, bar) and CHILD (id, type, parent_id), I would like to select...
3
by: davisford | last post by:
Hi, If I have something like this: <parents> <parent name="foo"> <children> <child>fred</child> <child>barney</child> <child>wilma</child>
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.