I am using Delphi 6 Enterprise and Interbase6.
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.
