IntegrityError when creating relationships between objects in flask-sqlalchemy
<p>I struggle to build geo object hierarchy for my ridership-forecasting model.</p>

<p>I have the following hierarchy of objects:</p>

<pre><code>class Geoobject(m.db.Model):
    __tablename__ = 'geoobjects'
    id = m.db.Column(m.db.Integer, primary_key=True)
    name = m.db.Column(m.db.String)

    def __init__(self, code: int, name: str):
        self.code = code
        self.name = name

class Region(Geoobject):

    __tablename__ = 'regions'
    id = m.db.Column(m.db.Integer, m.db.ForeignKey('geoobjects.id'),
                     primary_key=True)
    code = m.db.Column(m.db.Integer)

    def __init__(self, name: str, code: int):
        self.name = name
        self.code = code
        self.region_id = self.id

class TransportArea(Geoobject):

    __tablename__ = 'transport_areas'
    id = m.db.Column(m.db.Integer, m.db.ForeignKey('geoobjects.id'),
       primary_key=True, autoincrement=True)
    region = m.db.relationship('Region',
                          foreign_keys='Region.id', uselist=False)
    def __init__(self,
             name: str,
             region: Iterable[Region]):
        self.name = name
        self.region = region
</code></pre>

<p>When I try to add TransportArea object to session, I get the following error:</p>

<pre><code>IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: geoobjects.id 
[SQL: 'UPDATE geoobjects SET id=? WHERE geoobjects.id = ?'] [parameters: (2, 1)]
</code></pre>

<p>Any suggestions, how to fix this?</p>

<p><code>m</code> - is an instance of the model that contains flask app with db.</p>

<p>I've finally understood how it should work.
When I add <code>foreign_keys</code> keyword, I should specify a column for it and link it to the relationship. Working code is below:</p>

<pre><code>class TransportArea(Geoobject):

    __tablename__ = 'transport_areas'
    id = m.db.Column(m.db.Integer, m.db.ForeignKey('geoobjects.id'), 
                 primary_key=True, autoincrement=True)

    region_id = m.db.Column(m.db.Integer, m.db.ForeignKey('regions.id'))
    region = m.db.relationship('Region', foreign_keys=region_id)

    def __init__(self,
             name: str,
             region: Region
            ):
        self.name = name
        self.region = region
</code></pre>

python sqlalchemy flask-sqlalchemy