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.
精彩评论