CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
rdegges
rdegges

Reputation: 33824

How Can I Build a ForeignKey to a Table Which Has Composite Primary Key?

I'm having a lot of trouble figuring out how to properly build my ForeignKey column for a table I'm defining. I've outlined my models here (and put a comment next to the problematic line in my PhoneNumber model):

class AreaCode(db.Model):
    __tablename__ = 'areacodes'
    area_code = db.Column(db.Integer, primary_key=True)
    exchanges = db.relationship('Exchanges', backref='area_code')


class Exchange(db.Model):
    __tablename__ = 'exchanges'
    exchange = db.Column(db.Integer, primary_key=True)
    area_code_pk = db.Column(db.Integer, db.ForeignKey('areacodes.area_code'), primary_key=True)


class PhoneNumber(db.Model):
    __tablename__ = 'phonenumbers'
    phone_number = db.Column(db.Numeric(precision=4, scale=0), primary_key=True)
    exchange_pk = db.Column(db.Integer, db.ForeignKey('exchanges.exchange'), primary_key=True) # this doesnt work since Exchange has two primary keys

Here's what's happening:

My Exchange table has a composite primary key. This is required for my use case.

The PhoneNumber table I'm trying to define needs a ForeignKey to the Exchange table, but since the Exchange table has a composite primary key, I can't figure out how to make the relationship work.

Any guidance would be appreciated. Thank you.

Upvotes: 4

Views: 541

Answers (1)

rdegges
rdegges

Reputation: 33824

So, after speaking with several great people on the sqlalchemy mailing list, I finally got the above problem figured out. Below is the final version of my code which allows me to relate properly between the tables:

class AreaCode(db.Model):
    __tablename__ = 'areacodes'

    area_code = db.Column(db.Integer, primary_key=True)


class Exchange(db.Model):
    __tablename__ = 'exchanges'

    exchange = db.Column(db.Integer, primary_key=True)
    area_code_pk = db.Column(db.Integer, db.ForeignKey('areacodes.area_code'),
                             primary_key=True)
    area_code = db.relationship('AreaCode', backref=db.backref('exchanges', lazy='dynamic'))


class PhoneNumber(db.Model):
    __tablename__ = 'phonenumbers'
    __table_args__ = (
        db.ForeignKeyConstraint(
            ['exchange_exchange', 'exchange_area_code_pk'],
            ['exchanges.exchange', 'exchanges.area_code_pk'],
        ),
    )

    phone_number = db.Column(db.Integer, primary_key=True)
    exchange_exchange = db.Column(db.Integer, primary_key=True)
    exchange_area_code_pk = db.Column(db.Integer, primary_key=True)
    exchange = db.relationship('Exchange', backref=db.backref('phone_numbers', lazy='dynamic'))

Upvotes: 2

Related Questions