delete orphans from one to many

  • Thread starter Thread starter JethroUK©
  • Start date Start date
J

JethroUK©

I have a one (students) to many (quals) query (join type 1) which was
working fine, but now it isn't

when i deleted a record from the query - only the qual side was deleted
until it was the only student-qual, then both qual and student were
deleted - it behaved this way by default and was perfect

now it is deleting only the quals and leaving orphan students floating
around

how do i get it back to how it was?

p.s. I've just enforced referential integrity - set cascade delete - makes
no difference - still leaves orphaned students
 
Jethro, it strikes me that there is no direct relationship between students
and qualifications (or qualities, or whatever they are.)

It is perfectly valid to have a student who has no quals yet, and it is also
valid to have quals available that none of your student actually have.

You therefore need a StudentQual table - a junction between the other two.
This table would have a foreign key to the Student table, and another
foreign key to the Qual table. These relationships could both have cascading
deletes, so that when you delete a Student, their related records in
StudentQual are also deleted. Similarly, if you delete a Qual, the related
records in StudentQual are deleted. But deleting a Student has no impact on
the records in the Qual table, and deleting a Qual has no impact on the
records in the Student table.

A student who has no matching records in the StudentQual table is not an
orphan. He might just be untrained. A qual that has no matching records in
the StudentQual table is not an orphan. An example would be a new
course/qual that has not been assigned/recognised yet.

A StudentID value in StudentQual that has no matching StudentID in the
Student table would be an orphan record. But this will not occur if you have
a relationship between Student and StudentQual with referential integrity.
 
i prefer not to break the tables down anymore (the query supports a form and
would need a big re-write) - currently the Student!ID (primary key) is tied
to many Quals!ID (foreign key) by a direct tie (wont produce orphans either
side)

by default, this query did delete perfectly - but now it only deletes from
the quals side

is it at all possible that changes i've made to student table since, has
caused it to behave this way? or more likely that it never behaved any
differently and although i checked it first - i must've been mistaken and it
never deleted the student?

when i say student 'orphans' - i'm really talking about my own definition of
the word - since although i appreciate it's possible, i dont need a case of
a student without a qual or vice versa & the tie supported my needs exactly

ultimately i can use the form to run a search for orphans and delete them
periodically, but i would prefer to repair the relationship

i just cant figure out why it changed, and it's now making me wonder whether
it ever existed in the 1st place
 
Well, I can't tell you what existed in the first place.

Given your structure, you probably have a main form bound ot the Student
table, with a subform bound to the Qual table. If so, deleting form the
subform should delete from the Qual table.

If it did delete from the Student table as well, you could not have been
deleting a single subform record, since the "student" you were deleting
could have multiple subform records.

Since the Student is on the One side of the relation, it is perfectly
feasible and meaningful to have a student with no related records. You would
need to write code to avoid that situation if some business rule insisted
you did. That would be rather messy: any delete in the subform would need to
check to see if there are other related records, and if not, execute a
delete on the main form's table as well. Since delete's can be cancelled,
and multiple subform records can be deleted at once, this would involve
something in the subform's AfterDelConfirm event where you can check the
Status to see if the delete was for real. But the record you are seeking to
delete is the current record of the main form, so you will now end up with
#Deleted in the main form, so it needs to be requeried. So now the main form
is back to its first record.

This strikes me as both a bad design (no junction table) and a bad interface
(things disappearing from all over.)
 
the query is bound to a form - but not a subform

deleting a record via the form behaved exactly same way as deleting the
record direct from the query

as you say this should delete from the quals (many) side and leave the
students (one) side alone - i have to wonder whether it ever did delete the
student along with their last qual - and just to find out for sure - i
decided i will create a test database to see exactly how this relationship
works on delete, by default

let you know my findings
 
just checked it out - and you are quite right, it doesn't delete from the
student (one) side at all (irrespective of type of tie or referential
integrity) - i must've been mistaken all along

i cant leave orphaned students, since they are identified as unique and wont
be able to rejoin at a later date because the query wont produce them ( i
wont be able to find them) - which leaves me 2 choices:

1/ I can actually delete orphans very easily - by searching for a null
foreign key (only orphan students will have one)

2/ set the query to produce all students (orphans too) - in case they ever
want to rejoin - seems like better option so long as is doesn't mess up all
my statistics

thanks for your input
 
Back
Top