referencial integrity problem

[dan]

bottom line: cascade delete refuses to cascade delete!!

I have a medium complex database structure which has the
following tables:

* = primary key

members
memId *
clubs
clubId *
clubmembers
memId *
clubId *

clubattendance
memId *
clubId *
Date *

The tables have many more fields, but these are the
important ones. You may be able to guess, but these
tables have the following relationships:

members 1:M clubmembers
clubs 1:M clubmembers
clubmembers 1:M clubattendance

So far so good?

Now if I delete a record form the member table, I want the
deletion to cascade right through to the clubattendance
table. In order to do this, I have also set up the
relationships as follows:

members 1:M clubattendance

and likewise for clubs.

clubs 1:M clubattendance

I have also set up an automatic cascade update and delete
for these relationships.

However, Access complains when I try to delete a record
from either the members or clubs table where there are
related records in the clubattendance table. Access
reports that ref integ would be violated in the
clubattendance table so refuses to delete the record.

Any ideas? I appreciate that you'd probably have to draw
the relationships out yourself to fully understand, but
I'd appreciate any help.

Cheers,

Dan.

 

[Jen]

I believe the problem lies in the club_attendance entity
and how it's related to your other tables. You have the
following relationships:

members 1:M clubmembers
clubs 1:M clubmembers
clubmembers 1:M clubattendance ** problem **

You'll notice that clubmembers is your many table for
both members and clubs - commonly called an intersection
or junction table. This is correct. But where it goes
sour is in the clubmembers to clubattendance
relationship. Since clubmembers doesn't have a '1' side
to it (it's really a M:M) what it should be is:

members 1:M clubattendance
clubs 1:M clubattendance

This should fix the problem. I'm just not sure if you'll
be able to alter the table structure when you've already
set the ref. integrity rules. If you can't, just import
those tables into a blank access db and try doing your
restructure and setting up the ref. integrity rules in a
new database.

Hope that was clear!

Regards,
Jen

 

[Tim Ferguson]

members 1:M clubmembers
clubs 1:M clubmembers
clubmembers 1:M clubattendance ** problem **

It may well be part of the schema design, though, if members are not
allowed to attend at clubs of which they are not members. This seems quite
a reasonable rule, and there is no other way implementing it.

The original poster does not say that he has set cascade delete on this
third relationship, and doing so should fix the problem. When removing a
Members record causes the ClubMembers record to be deleted, that should
cascade into the ClubAttendance table.

I am not at all sure that it's a good idea in the generality of cases
though -- with one mistimed sweep of the mouse it's possible to trash many
hundreds of records, and I just feel that a responsible developer should
put a bit more control in. Personal taste, I guess.

All the best


Tim F

 

[Jen]

I agree with your point of putting more control in. In
general, I don't use the ref. integrity feature. I was
just trying to answer a question based on the information
given.

Have a nice day!

 

[TC]

Have you:

o in clubmembers, defined the 2 key fields as a composite (2-field) primary
key?

o in clubattendance, defined the 3 key fields as a composite (3-field)
primary key?

o related the composite primark keys, not their individual fields
individually?

HTH,
TC