lundi 3 août 2015

How to correctly add Foreign Key constraints to SQLite DB using SQLAlchemy

I'm very new to SQLAlchemy and I'm trying to figure it out.

Please have in mind the following test setup:

class Nine(Base):
    __tablename__ = 'nine'

    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'

    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'

    three_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), primary_key=True, 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)

All tables are empty. Then, the following code statements:

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

Can somebody please explain why is the above code snippet executing without errors? Should't the FK constraints stop from inserting a new row into seven or three? I assume there is something wrong with how the FKs are described in the classes themselves, but I don't know where the problem is (and how to fix it).

Aucun commentaire:

Enregistrer un commentaire