CODEWITHSUNDEEP
pythonsqlpython-3.xsqlalchemyforeign-keys
Mahi
Mahi

Reputation: 21893

Child with multiple foreign keys to parent?

I have a company with a country code, a business ID, and a list of employees:

class Company(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    business_id = db.Column(db.String(20), nullable=False)
    country_code = db.Column(db.String(2), nullable=False)
    employee = db.relationship('Employee', backref='company')

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    company_id = db.Column(db.Integer, db.ForeignKey('company.id'))

But say the data changes contantly, and the id isn't always the same for the same company (a company might get removed from the database, then re-added later on with different id). However, the combination of country_code and business_id is always quaranteed to be both unique, and constant for the same company.

How do I create two foreign keys for Employee that point into Company's country_code and business_id?

I tried doing this through an older StackOverflow question, but it raised an error that 'str' shouldn't be used:

class Company(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    business_id = db.Column(db.String(20), nullable=False)
    country_code = db.Column(db.String(2), nullable=False)
    employee = db.relationship('Employee', backref='company', foreign_keys=['Company.business_id', 'Company.country_code'])

However, Python's syntax prevents me from referring to the class itself.

Upvotes: 1

Views: 65

Answers (1)

van
van

Reputation: 76992

I am not sure exactly what you are asking, but the code sample below does two things, which I think would be helpful:

  1. define a composite foreign key
  2. indicate which foreign key to use for the relationship between Company and Employee

Model:

class Company(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    business_id = db.Column(db.String(20), nullable=False)
    country_code = db.Column(db.String(2), nullable=False)
    name = db.Column(db.String(), nullable=False)

    employee = db.relationship(
        'Employee', backref='company',
        # 2. indicate FK to use below
        foreign_keys=lambda: [Employee.business_id, Employee.country_code],
        # foreign_keys=lambda: Employee.company_id,
    )


class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    company_id = db.Column(db.ForeignKey('company.id'))
    name = db.Column(db.String(), nullable=False)

    business_id = db.Column(db.String(20), nullable=False)
    country_code = db.Column(db.String(2), nullable=False)

    # 1. define a composite foreign key
    __table_args__ = (
        db.ForeignKeyConstraint(
            ['business_id', 'country_code'],
            ['company.business_id', 'company.country_code']
        ),
    )

Upvotes: 1

Related Questions