开发者

Referential Integrity does not work in N:N relation

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?


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜