CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
Bruno Thomas
Bruno Thomas

Reputation: 1229

sqlalchemy secondary table with compound keys

I have a multi-customer plateform, persons that can have several labels, and let say a foo object with one label. I don't want users from each customer to share their contacts (persons) or labels or foo with other customers. Each label name is unique per customer.

How could I have a compound key for label (name, customer_name) and a n-m relationship between persons and labels ?

I tried this :

enter image description here

person_label_table = Table('person_label', Base.metadata,
                       Column('person_id', Integer, ForeignKey('person.id'), primary_key=True),
                       Column('name', Unicode(32), ForeignKey('label.name'), primary_key=True),
                       Column('customer_name', String(32), ForeignKey('label.customer_name'), primary_key=True)
                       )

class Person(Base, SaveMixin):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    labels = relationship('Label', secondary=person_label_table)

class Label(Base, SaveMixin):
    __tablename__ = 'label'
    name = Column(Unicode(32), primary_key=True)
    customer_name = Column(String(32), ForeignKey('customer.name'), primary_key=True)
    color = Column(String(32))

But I have this error :

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'label' and 'person_label'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

I tried also with a more classic link table person_label(label_id, person_id) and adding an id to the labels but the label id's must be loaded to the front web else when I do a session.merge() with labels (without id) I have :

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: label.name, label.customer_name [SQL: 'INSERT INTO label (name, customer_name, color) VALUES (?, ?, ?)'] [parameters: ('foo', 'bar', 'grey')]

So how would you handle this case ?

Thank you for your time.

EDIT : Just in case it could help, after the Brendan Abel response, and the shown code, I had this error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Person.labels - there are multiple foreign key paths linking the tables via secondary table 'person_label'.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

so I changed the definitions of labels in person:

labels = relationship('Label', secondary=person_label_table, foreign_keys=[id]):

But I had then :

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Person.labels - there are no foreign keys linking these tables via secondary table 'person_label'.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.

I ended up with (adding the primaryjoin/secondaryjoin on person and removing the ForeignKey on compound keys) :

class Person(Base, SaveMixin):
    __tablename__ = 'person'
    labels = relationship('Label', secondary=person_label_table,
                      primaryjoin='and_(Person.id == person_label.c.person_id)',
                      secondaryjoin='and_(person_label.c.name == Label.name, person_label.c.customer_name == Label.customer_name)')

person_label_table = Table('person_label', Base.metadata,
                       Column('person_id', Integer, ForeignKey('person.id'), primary_key=True),
                       Column('name', Unicode(32), primary_key=True),
                       Column('customer_name', String(32), primary_key=True),
                       ForeignKeyConstraint(['name', 'customer_name'], ['label.name', 'label.customer_name'])
                       )

Upvotes: 3

Views: 1025

Answers (1)

Brendan Abel
Brendan Abel

Reputation: 37599

You'll have to explicitly indicate the composite relationship using a ForeignKeyConstraint:

person_label_table = Table('person_label', Base.metadata,
    Column('person_id', Integer, ForeignKey('person.id'), primary_key=True),
    Column('name', Unicode(32), primary_key=True),
    Column('customer_name', String(32), primary_key=True),
    ForeignKeyConstraint(['name', 'customer_name'], ['label.name', 'label.customer_name'])
)

The docs provide some examples of this.

It’s important to note that the ForeignKeyConstraint is the only way to define a composite foreign key. While we could also have placed individual ForeignKey objects on both the invoice_item.invoice_id and invoice_item.ref_num columns, SQLAlchemy would not be aware that these two values should be paired together - it would be two individual foreign key constraints instead of a single composite foreign key referencing two columns.

Upvotes: 3

Related Questions