在SQLite和SQLAlchemy中，对具有复合主键的表(同时也是多个表的外键)进行级联删除 &g

Question

我正试图从一个表中级联删除记录，其中主键由指向两个不同表的两个外键组成。我使用的数据库是使用SQLAlchemy反射的SQLite。我使用的表声明为:

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))

通过automap:

完成镜像。

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

当试图删除一个记录在级联它给了我一个错误:

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

返回me的错误是:

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

我不确定我想做的是可能的还是它是一个自动映射反射问题。或者如果SQLite不支持此操作

谢谢。

Answer 1

最后,我解决它通过添加非空的外键字段。

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