Relationship woes

[Guest]

Hi, I'm creating a very simple database with some students, their locations
etc. I have three tables set up, and I have them related. The main table is
'demo', and two sub tables are 'school' and 'info'. I have 'demo' related to
both 'school' and 'info' with a one to one relationship.

I've created queries and forms for input, and the data enters the database
fine. I have yet to create delete queries and that's where my problem comes
in. As a test, I tried to delete a record in the school table.
Unfortunately I was sucessful. My thoughts were since I have the school
table related to the main 'demo' table, I would only be able to delete a
record if I deleted the record from the demo table. However that's not
what's happening.

As I stated earlier, I have one to one relationships between the tables, and
I've enforced referential integrity. I've also checked the 'cascade delete
related records. Should that box be unchecked?

I appreciate any help you can offer. Sorry to be so long winded.

T.

 

[Guest]

I am guessing you are using some form of foriegn key from the two "sub
tables". Deleting while enforcing referential intergrity allows you to only
delete from the "outside" of the relationship "in". This means you can't
delete from your "demo" table without deleting the linked records in the
other two tables, therefore deleting the relationships between the records.

Hope this helps a little.

 

[Ed Warren]

It would help if we could have more insight to what you are trying to
relate.

Your described relationships
-----------------
Demo 1:1 School (Each demo is related to 0 to 1 School)
Demo 1:1 Info (Each demo is related to 0 to 1 Info records)
-----------------

Don't really give us much to work with. A more usual situation would be:
------------------------------
Demo 1: M Schools (each demo is related to 0 to many schools)
Demo 1: M Info (each demo is related to 0 to many Info records)
------------------------------------
If Demo is the parent, when you delete a row from the school table, the row
in the school table is deleted and the row in the Demo table is left alone.
If the School table is the parent, when you delete a row from the School
table the cascade delete takes over and deletes the associated row in the
Demo table.
(Your 1:1 relationship is just a special case of the 1:M realtionship
between Demo and School)

Cascade delete needs to be used very carefully.

Ed Warren

 

[John Vinson]

Hi, I'm creating a very simple database with some students, their locations
etc. I have three tables set up, and I have them related. The main table is
'demo', and two sub tables are 'school' and 'info'. I have 'demo' related to
both 'school' and 'info' with a one to one relationship.

As Ed and Ghillie say, you may be heading in the wrong direction here.
One to one relationships are QUITE uncommon. If you're not familiar
with the terms "Subclassing" or "Field level security", then your
relationships are probably incorrect. If there is truly only one
School for each Demo, and every Demo must have a different School (or
no school at all), then why not just include the School fields in the
Demo table?

What real-life entities do these tables represent? If a Demo refers to
a student, is it correct to say that each school must have exactly
one, and no more, students? That would seem rather odd.


John W. Vinson[MVP]