CODEWITHSUNDEEP
sql-server-2012sqlalchemyflask-sqlalchemy
Milo Hou
Milo Hou

Reputation: 339

Flask-SqlAlchemy composite key one to many relationship Sql Server

I'm currently getting an error, I'm using sql server and trying to model a simple Parent with an array of Children:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Parent.children- there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

my classes are set up simply as follows:

class Parent(db.Model):
    __tablename__ = "parent"
    parentId = db.Column(db.Integer, primary_key=True)
    parentVersion = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child', backref="parent",lazy=True)


class Child(db.Model):
    __tablename__ = "child"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(512), nullable=False)

    parentId = db.Column(db.Integer, nullable=False)
    parentVersion = db.Column(db.Integer, nullable=False)
    ForeignKeyConstraint(['parentId', 'parentVersion'], ['parent.parentId', 'parent.parentVersion']

I've tried fiddling with declaring the relationship and foreign key in several ways but i always get an error, what is the correct way to do this?

Upvotes: 0

Views: 940

Answers (2)

mochatiger
mochatiger

Reputation: 304

You are missing adding the ForeignKeyConstraint to the table args, and you are using camel case, not snake case. And you don't need the __tablename__ with Flask-SQLAlchemy.

Try:

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    version = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child', backref="parent", lazy=True)

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(512), nullable=False)

    parent_id = db.Column(db.Integer, nullable=False)
    parent_version = db.Column(db.Integer, nullable=False)
    __table_args__ = (
    db.ForeignKeyConstraint(
            ['parent_id', 'parent_version'],
            ['parent.id', 'parent.version']
        ),
    )

Upvotes: 0

dariokl
dariokl

Reputation: 59

Your forgot to add a foreign key:

parentId = db.Column(db.Integer, db.ForeignKey("parent.id))

There is a lot of documentation material regarding this topic, if there is still anything unclear to you.

Upvotes: 0

Related Questions