I am using Delphi 6 Enterprise and Interbase6.

[delphiman]

Referential integrity in a SQL DataBase

Is there a SQL guru out there who can please tell me how to create referential ingerity
between table PARENT, CHILD, GCHILD and GGCHILD in a SQL DataBase called PChild.GDB?

What has to be born in mind is the following.

In table PARENT field P_NO has to be Unique - as there can only be ONE "Father 1" in the
table. (i.e. P_NO can only have the value "1" ONCE.)

But in table CHILD field C_NO can NOT be unique because there will be a "child 1" for each
father. Same applying to field GC_NO in table GCHild (GrandChild). And GC_NO in table
GGChild (GreatGrandChild).

Table GCHILD needs field P_NO to relate it to P_NO in table PARENT (being it's father)
whilst also needing a field C_NO to relate it to C_NO in table CHILD (being IT'S father)
along with GC_NO.

Equally table GGCHILD should have similar referential integrity to the above through
fields P_NO, C_NO and GC_NO.

Whilst one can create referential integrity between table CHILD and PARENT as follows ....

"ALTER TABLE CHILD ADD FOREIGN KEY (P_NO) REFERENCES PARENTS (P_NO);"

.... what does one require to create the same effect bewteen the other tables? Bearing in mind
that the following won't work whilst C_NO (and G_NO) are not unique ..

ALTER TABLE GCHILD ADD FOREIGN KEY (C_NO) REFERENCES CHILD (C_NO);
ALTER TABLE GGCHILD ADD FOREIGN KEY (GC_NO) REFERENCES GCHILD (GC_NO);

CONSTRAINT CHILD_PARENT_PK PRIMARY KEY (P_NO),
CONSTRAINT GCHILD_CHILD_PK PRIMARY KEY (C_NO),
CONSTRAINT GGCHILD_GCHILD_PK PRIMARY KEY (GC_NO),

Thanks in advance.

Terry.

 

[Oppenhiemer]

Hi Terry -

The relationship you describe beaks referential rules. You need to associate the GCHILD entity with the child - not the parent. So ..

"ALTER TABLE GCHILD ADD FOREIGN KEY (C_NO) REFERENCES CHILD (C_NO)"

Is wrong, but Should be associated with the primary key in the Child table (the unique key in the child table.) Here you are actually trying to associate it with the foreign key in the child table - which is not unique. Here is how it should be..

"ALTER TABLE GCHILD ADD FOREIGN KEY (CID) REFERENCES CHILD (CID)"

Where CID is the primary key in the child table.

Opp.

 

[delphiman]

Thanks for that.

But if you look at your suggestion again you will find that
it will not leave me with Child 1 (of FATHER 2) ALSO being numbered "1" as is the case with Child 1 of FATHER 1.

Your suggestion leaves Child 1 (of Father 2) to become Child 3 - merely because Father 1 ALREADY HAS two children in table CHILD (numbered 1 and 2) - which is unacceptable.

>Should be associated with the primary key in the Child table (the unique key in the child table.)

I can not have C_NO (GC_NO and GGC_NO) being UNIQUE (as explained in the first paragraph above) and therefore C_NO, GC_No and GGC_No them can NOT be primary keys.

>CID is the primary key in the child table.

I have a primary key (Cakv) BUT it serves only to prevent key-violations in the table whilst leaving C_NO free to be "1" (whatever) REPEATEDLY.

And because it is a primary key it is automatically
unique. Which doesn't matter as it doesn't relate to anything otherwise.

Perhaps you would like to have a look at my thread102-296246

 

[Oppenhiemer]

I must admit its tricky to follow the details of the relationships you descibe. But if I understand you correctly (and you wanted to have "Child1" appearing > once in the same table - the answer may be to use a combined key.

If you use CID and P_NO as the primary key in the Child table - this will allow the relationship you describe ("Child 1" can appear many times - but associated with a different parents.) The rest of the relationship stills stands.

Opp.

 

[delphiman]

>I must admit its tricky to follow the details of the relationships you descibe.

It is so simple really. I really thought that the Parent/Child/GrandChild/GreatGrand-child analogy is very easy to understand.

In fact my application/s have feathers to do with families.

Rather a matter of numerous aircraft companies (PARENT) having numerous aircraft (CHILDREN) with numerous modules
(Wings, undercarriage, etc. - GRAND-CHILDREN) each of which can be drilled-down further to (say) Ailerons GREAT-GRAND-CHILDREN) which can be drilled further down to Bearings GREAT-GREAT-GREAT GRAND-CHILDREN. Etc. etc.

From which you will appreciate that I can't have "Engine 1"
of Boeing 6 (P_NO = 6 in the Parent file) being numbered Engine "21" (GC_NO in GCHILD) simply because there are already 20 engines in the data-base and relating to 5 other aircraft.

And if (say) a local school has all the children in attendance on their data-base I am sure you would not be happy to find your 1st grand-child being numbered as being your 244th grand-child - because there are already 243 children on the school's DataBase.

As I say (in June 17th of this thread) ...
>But in table CHILD field C_NO can NOT be unique because >there will be a "child 1" for each father. Same applying to field GC_NO in table GCHild (GrandChild). And GC_NO in table GGChild (GreatGrandChild).

>If you use CID and P_NO as the primary key in the Child table - this will allow the relationship you describe ("Child 1" can appear many times - but associated with a different parents.)

Correct.

But you then still have to relate the GRAND Children to each Father and Child? So that the FIRST Grand-child of EACH Child 1 ALSO remains "Grand-child ONE" for each child.

>The rest of the relationship stills stands.

Can't happen. Try it yourself.

I really appreciate your interest and time. Thanks!

If you like I'll happily send you the model referred to in
my Thread102-296246 to play with. Then you will see what I mean.

P.S. I have resolved the "delay-action" problem mentioned in the thread.