如何使用SQLAlchemy正确地将外键约束添加到SQLite DB

请记住以下测试设置：

class Nine(Base):
    __tablename__ = 'nine'
    __table_args__ = (sqlalchemy.sql.schema.UniqueConstraint('nine_b',name='uq_nine_b'),)

    nine_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),primary_key=True,autoincrement=False,nullable=False)
    nine_b = sqlalchemy.Column(sqlalchemy.String(20),nullable=False)


class Seven(Base):
    __tablename__ = 'seven'
    __table_args__ = (sqlalchemy.sql.schema.PrimaryKeyConstraint('seven_a','seven_b'),sqlalchemy.sql.schema.Index('fk_seven_c_nine_a_idx','seven_c'),)

    seven_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),nullable=False)
    seven_b = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),nullable=False)
    seven_c = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),sqlalchemy.ForeignKey('nine.nine_a'),nullable=False)
    seven_d = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),nullable=False)

    nine = sqlalchemy.orm.relationship(Nine,backref=sqlalchemy.orm.backref('seven'),uselist=False)


class Three(Base):
    __tablename__ = 'three'
    __table_args__ = (sqlalchemy.sql.schema.UniqueConstraint('three_b','three_c',name='uq_three_b_c'),sqlalchemy.sql.schema.Index('fk_three_c_seven_a_idx','three_c'),)

    three_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),autoincrement=True,nullable=False)
    three_b = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),nullable=False)
    three_c = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(),sqlalchemy.ForeignKey('seven.seven_a'),nullable=False)

    seven = sqlalchemy.orm.relationship(Seven,backref=sqlalchemy.orm.backref('three'),uselist=False)

这转化为以下DDL：

CREATE TABLE nine (
    nine_a INTEGER NOT NULL,nine_b VARCHAR(20) NOT NULL,PRIMARY KEY (nine_a),CONSTRAINT uq_nine_b UNIQUE (nine_b)
);

CREATE TABLE seven (
    seven_a INTEGER NOT NULL,seven_b INTEGER NOT NULL,seven_c INTEGER NOT NULL,seven_d INTEGER NOT NULL,PRIMARY KEY (seven_a,seven_b),FOREIGN KEY(seven_c) REFERENCES nine (nine_a)
);

CREATE INDEX fk_seven_c_nine_a_idx ON seven (seven_c);

CREATE TABLE three (
    three_a INTEGER NOT NULL,three_b INTEGER NOT NULL,three_c INTEGER NOT NULL,PRIMARY KEY (three_a),CONSTRAINT uq_three_b_c UNIQUE (three_b,three_c),FOREIGN KEY(three_c) REFERENCES seven (seven_a)
);

CREATE INDEX fk_three_c_seven_a_idx ON three (three_c);

所有表都是空的.然后,以下代码语句：

session.add(Nine(nine_a=1,nine_b='something'))
session.add(Nine(nine_a=2,nine_b='something else'))
session.commit()

session.add(Seven(seven_a=7,seven_b=7,seven_c=7,seven_d=7))
session.commit()

session.add(Three(three_a=3,three_b=3,three_c=3))
sessionDB.commit()

有人可以解释为什么上面的代码片段没有错误执行？ FK约束不应该停止将新行插入七或三个？我假设在类本身中如何描述FK有什么问题,但我不知道问题出在哪里(以及如何修复它).

[编辑1]

为所有类添加__table_args__(忘记包含它们).

[编辑2]

添加DDL以供进一步参考.

要启用,请按以下文档操作：http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support

这是官方文档的复制粘贴：

SQLite在为表发出CREATE语句时支持FOREIGN KEY语法,但是默认情况下,这些约束对表的操作没有影响.

SQLite上的约束检查有三个先决条件：

至少必须使用版本3.6.19的SQLite
必须在未启用SQLITE_OMIT_FOREIGN_KEY或SQLITE_OMIT_TRIGGER符号的情况下编译SQLite库.
必须在使用前在所有连接上发出PRAGMA foreign_keys = ON语句.
SQLAlchemy允许通过使用事件自动为新连接发出PRAGMA语句：

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine,"connect")
def set_sqlite_pragma(dbapi_connection,connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!