Deleting records in a many to many relationship Revisited

[Bam Bam]

Hi all,

Thankyou to D.J and Gunny for your much appreciated suggestions. However,
there seems to be a catch with the suggested SQL string.

Here is the issue I was attempting to resolve: I have a music database with
two main tables (tblArtists and tblRecordings) joined by a junction table
(tblLinkArtist_Recording) used to resolve the many to many relationship (as
an Artist can have many recordings and a Recording(Various Artists CD for
example) can have many Artists). Each record in the junction table contains
only the ArtistID and the RecordingID as a composite primary key, no other
fields. I would like to be able to delete the associated Recordings when I
choose to delete an Artist.

Assuming 'intArtistID' is holding the ArtistID of the current Artist record:

DELETE * FROM tblRecordings WHERE RecordingID IN
(SELECT RecordingID FROM tblLINKArtist_Recording WHERE ArtistID=" &
intArtistID)

the problem is that even with Referencial Integrity enforced between
tblArtists, tblRecordings and the junction table, the recording gets deleted
even though there remain Artists who are linked to that Recording.

i.e. the recording is associated with the three ArtistID's in the junction
table(example below) and all these associations get deleted.

ArtistID RecordingID

304 268
305 268
306 268

Although Artist 304 is deleted, how to keep the associations between 305,
306 and the Recording 268 alive?

 

[Van T. Dinh]

I think you may need to re-think your algorithm.

If you delete the Recording (RecordingID) but you still want to leave the
associations of that Recording (RecordingID) with other Artists, you will
end up with lots of orphaned Records in tblLinkArtist_Recording, i.e.
(Child) Records in this Table that are related to *non-existing* (Parent)
RecordingID.

You obviously use the R.I. with Cascade Delete between tblRecordings and
tblLinkArtist_Recording. Of course, the whole purpose of the R.I. with
Cascade Delete is to prevent orphaned Records!