Referential Integrity does not work in N:N relation

Question

This is ms create table script:

It is a N:M relation between the SchoolclassCode and the Pupil table

CREATE TABLE Schoolclasscode (
  schoolclassId    integer PRIMARY KEY AUTOINCREMENT NOT NULL 
);

CREATE TABLE SchoolclasscodePupil (
  pupilId_FK        integer NOT NULL,
  schoolclassId_FK  integer NOT NULL,
  /* Foreign keys */
  FOREIGN KEY (schoolclassId_FK)
    REFERENCES Schoolclasscode(schoolclassId)
    ON DELETE CASCADE
    ON UPDATE NO ACTION, 
  FOREIGN KEY (pupilId_FK)
    REFERENCES pupil(pupilId)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
);

CREATE TABLE pupil (
  pupilId           integer PRIMARY KEY AUTOINCREMENT NOT NULL  
);

When I delete a SchoolclassCode object in my code:

public void DeleteSchoolclass(int schoolclassCodeID, SQLiteConnection con)
        {
            using (SQLiteCommand com = new SQLiteCommand(con))
            {
                com.CommandText = "DELETE FROM schoolclasscode WHERE SchoolclassId = @SchoolclassId";
                com.Parameters.Add(new SQLiteParameter("@SchoolclassId", schoolclassCodeID));
                com.ExecuteNonQuery(); 
            }
        }

The entry in the schoolclasscode table is deleted. But nothing more. I can even additionally delete the schoolclasscodeId_FK in the SchoolclasscodePupil but no pupils were deleted by a cascade delete constraint.

What do I wrong?

Ben Voigt · Accepted Answer · 2011-10-01T19:42:59.587

1

In an N:M relation, either N or M may be zero. Referential integrity has not been violated.

Deleting a class will deregister all pupils from that class. Similarly deleting a pupil will unroll them from all classes. But deleting a pupil can never cause a cascade to cancel a class, nor can deleting a class expel a pupil. Even if it's the last class the pupil was taking, you're left with a pupil who has zero classes, which is valid under the referential integrity rules.

edited Oct 01 '11 at 19:42

answered Oct 01 '11 at 19:27

Ben Voigt

277,958
43
419
720

Zero classes is probably a pretty common state for new students, anyway – Merlyn Morgan-Graham Oct 01 '11 at 19:46
@Ben yeah the delete cascade on the pupil were rather a desperate move from me haha ;-) of course its not logical but well... the devil is in the detail. Of course the pupils without any class won`t be loaded anymore in my program. but they are still there in the table thats what bothering me first because they take space. A pupil might have incident reports which again has attached documents... Yes of course you might say now that practically a pupil is not often deleted so why should I care about the remaining documents... Maybe... – msfanboy Oct 01 '11 at 20:24
Ok lets assume I dont bother the remaining documents. Then I do not care about RI in this case right :) – msfanboy Oct 01 '11 at 20:25

Referential Integrity does not work in N:N relation

1 Answers1