Unable to create self referencing foreign key in flask-sqlalchemy

user_535962 0
Viewed 13795

I have a model Region and each Region can have sub-regions. Each sub-region has a field parent_id which is the id of its parent region. Here is how my model looks like

class Region(db.Model):
    __tablename__ = 'regions'
    __table_args__ = {'schema': 'schema_name'}
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    parent_id = db.Column(db.Integer, db.ForeignKey('regions.id'))
    parent = db.relationship('Region', primaryjoin=('Region.parent_id==Region.id'), backref='sub-regions')
    created_at = db.Column(db.DateTime, default=db.func.now())
    deleted_at = db.Column(db.DateTime)

Bu when i try to do db.create_all i get this error sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'regions.parent_id' could not find table 'regions' with which to generate a foreign key to target column 'id'

Why cant it find regions when i am specifying it in __tablename__? I am using flask-sqlalchemy version 1.0

EDIT -- i removed the line 

__table_args__ = {'schema': 'schema_name'}

from my code and it works. Beats the hell out of me.

flask-sqlalchemy flask sqlalchemy python
4 Answers

If you use a schema for any table, other tables that have foreign keys referencing those schema tables must provide the name of the schema. See the docs here 

class Table(db.Model):
    __tablename__ = 'table_1_name'
    __table_args__ = {'schema': 'my_schema'}

    id = Column('id', Integer, primary_key=True)
    ...

class AnotherTable(db.Model):
    __tablename__ = 'table_2_name'
    # Doesn't matter if this belongs to the same or different schema
    # __table_args__ = {'schema': 'my_schema'}

    id = Column('id', Integer, primary_key=True)
    t1_id = Column(Integer, ForeignKey('my_schema.table_1_name.id'))
    ...

Works for both SQLAlchemy and Flask-SQLAlchemy. Hope this helps. :D

user_33403440
Related