Flask SQLAlchemy one to one relationship with composite primary key

Question

Let's say I have 2 tables, 1 in which I store some houses and 1 in which I want to store only the ones that were sold, therefore I have a one to one relationship and the code that I wrote looks like this (actual names have been changed and useless columns removed in order to show this example):

class Houses(db.Model):
    __tablename__ = 'Houses'
    PropertyID = db.Column(db.Integer, primary_key=True, autoincrement=False, nullable=False),
    StateID = db.Column(db.Integer, primary_key=True, autoincrement=False, nullable=False)
    OtherFields = db.Column(db.String(255), nullable=False)

class SoldHouses(db.Model):
    __tablename__ = 'SoldHouses'
    PropertyID = db.Column(db.Integer, db.ForeignKey('Houses.PropertyID'), primary_key=True, nullable=False)
    StateID = db.Column(db.Integer, db.ForeignKey('Houses.StateID'), primary_key=True, nullable=False)

I have set up migrations properly using Flask-Script (https://flask-migrate.readthedocs.io/en/latest/) and after I run (I already ran init previously):

python Models.py db migrate
python Models.py db upgrade

I get the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]There are no primary or candidate keys in the referenced table 'Houses' that match the referencing column list in the foreign key 'FK__Unas__Marke__7C4F7684'. (1776) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not create constraint or index. See previous errors. (1750)")

Do I not create the Foreign Key properly or what is going on ?

Edit: I've added this code to my SoldHouses table, as suggested by the user van, but I get the exact same error:

__table_args__ = (
    db.ForeignKeyConstraint(
        ['PropertyID', 'StateID'],
        ['Houses.PropertyID', 'Houses.StateID'],
    ),
)

This is the full error message:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]There are no primary or candidate keys in the referenced table 'Houses' that match the referencing column list in the foreign key 'FK__SoldHouse__Prope__1A14E395'. (1776) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not create constraint or index. See previous errors. (1750)") [SQL: '\nCREATE TABLE [SoldHouses] (\n\t[PropertyID] INTEGER NOT NULL, \n\t[StateID] INTEGER NOT NULL, \n\tPRIMARY KEY ([PropertyID], [StateID]), \n\tFOREIGN KEY([PropertyID]) REFERENCES [Houses] ([PropertyID]), \n\tFOREIGN KEY([StateID]) REFERENCES [Houses] ([StateID])\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)

Final Edit: In the class definition of the tables the 'db.ForeignKey()' attribute also has to be removed in order for the code to work! (keep the foreign key constraint only in the _table_args_ )

Please see [this answer](https://stackoverflow.com/a/27304661/99594) — van, Sep 12 '18 at 16:04

Flask SQLAlchemy one to one relationship with composite primary key

0 Answers0