Search code examples
sqlitesqlalchemykeycascadeautomap

Cascading delete on tables with composite primary key that are also foreign keys of multiple tables, in SQLite and SQLAlchemy

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.

Solution

  • 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,