Cascading delete on tables with composite primary key that are also foreign keys of multiple tables,-CodePudding

I am trying to cascade delete records from a table where the primary key is made up of two foreign keys pointing to two different tables. The database I am using is SQLite using a reflection of SQLAlchemy. The tables I am using are declared as:

CREATE TABLE "IndividualSample" (
     "id_execution" INTEGER,
     "id_individual"    INTEGER,
     "n_vce"    INTEGER,
     PRIMARY KEY("id_execution","id_individual","n_vce") ON CONFLICT IGNORE,
     CONSTRAINT "fk_individual" FOREIGN KEY("id_individual") REFERENCES "Individual"("id") ON UPDATE CASCADE ON DELETE CASCADE,
     CONSTRAINT "fk_execution" FOREIGN KEY("id_execution") REFERENCES "ExecutionVCE"("id") ON UPDATE CASCADE ON DELETE CASCADE)

CREATE TABLE "ExecutionVCE" (
     "id"   INTEGER,
     "type"  VARCHAR(12) CHECK(type IN ("Interaction", "Normal")) NOT NULL DEFAULT "Normal",
     "fixed_factor"  VARCHAR(20) DEFAULT NULL,
     "environments"   VARCHAR(50) DEFAULT NULL,
     "generations"   VARCHAR(50) DEFAULT NULL,
     "datetime_start"   DATETIME DEFAULT NULL,
     "datetime_end" DATETIME DEFAULT NULL,
     "incidents"    VARCHAR(2000) DEFAULT NULL,
     "result_path"  VARCHAR(200) DEFAULT NULL,
     "n_vce_start"  INTEGER,
     "status_one"    TINYINT,
     "r_factors"   VARCHAR(300),
     "f_factors"   VARCHAR(300),
     "nulls_allowed" TINYINT,
     "individuals" INTEGER DEFAULT 0,
     PRIMARY KEY("id" AUTOINCREMENT))

The mirroring is done with automap:

Base = automap_base()
Base.prepare(self.engine, reflect=True)

self.ExecutionVCE = Base.classes.ExecutionVCE
self.IndividualSample = Base.classes.IndividualSample

And when trying to delete a record in cascade it gives me an error:

eval = self.session.query(self.ExecutionVCE).filter(self.ExecutionVCE.id == eval_idx).first()
self.session.delete(eval)
self.session.commit()

The error that returns me is:

AssertionError: Dependency rule tried to blank-out primary key column 'IndividualSample.id_execution' on instance '<IndividualSample at 0x1298396bfd0>'

I'm not sure if what I'm trying to do is possible or if it's an automap reflection problem. Or if SQLite simply doesn't support this operation.

Thanks.

CodePudding user response：

In the end, I solved it by adding NOT NULL to the foreign key fields.

"id_execution"  INTEGER NOT NULL,
"id_individual" INTEGER NOT NULL,