5

Let's say I have the following model:

class Molecule(Base):
   db = Column(Integer, primary_key=True)
   id = Column(Integer, primary_key=True)
   data = Column(Integer)

class Atom(Base):
   id = Column(Integer, primary_key=True)
   weight = Column(Integer)

And I want to establish a many-to-many relationship between Molecule and Atom, what would be the best way to do it? Notice that the primary key of Molecule is composite.

Thanks

Christian Teijon
  • 456
  • 4
  • 12
  • 1
    Very similar to: http://stackoverflow.com/questions/10525797/sqlalchemy-relation-table-with-composite-primary-key – van Mar 07 '13 at 10:17
  • Oh, you're right, I hadn't seen that question (and I did search for it). Anyway I think my question and answer is way more concise so I'll keep them, – Christian Teijon Mar 07 '13 at 10:37

3 Answers3

12

many-to-many association tables should be defined like this:

molecule2atom = Table(
  'molecule2atom',
  Base.metadata, 
  Column('molecule_db', Integer),
  Column('molecule_id', Integer),
  Column('atom_id', Integer, ForeignKey('atom.id')),
  ForeignKeyConstraint( 
    ('molecule_db', 'molecule_id'),
    ('molecule.db', 'molecule.id')  ),
)

And add the relatiohship to one of the models as usual, for example, in Class Atom add:

molecules = relationship("Molecule", secondary=molecule2atom, backref="atoms")
Jacob Pavlock
  • 673
  • 8
  • 20
Christian Teijon
  • 456
  • 4
  • 12
2

I liked the solution given here better - composite key many to many

Burple
  • 31
  • 4
  • Links can go dead - please copy + paste the important bits into your answer. – Sora2455 Oct 26 '20 at 07:34
  • phew thanks for this. Looked through ~10 SO threads and none clearly explained the (I'm assuming very common) situation of establishing a many-to-many relationship where at least one table has a composite primary key. That article clearly explains the problem, a solution, common warnings, and common mistakes. – Brent Feb 09 '22 at 20:30
0

If you're using an association table or fully declared table metadata, you can use the primary_key=True in both columns, as suggested here.

Association table example:

employee_role = db.Table(
    "employee_role",
    db.Column("role_id", db.Integer, db.ForeignKey("role.id"), primary_key=True),
    db.Column("employee_id", db.Integer, db.ForeignKey("agent.id"), primary_key=True),
)

Metadata example:

# this is using SQLAlchemy
class EmployeeRole(Base):
    __tablename__ = "employee_role"

    role_id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, primary_key=True)

# this is using Flask-SQLAlchemy with factory pattern, db gives you access to all SQLAlchemy stuff
class EmployeeRole(db.Model):
    __tablename__ = "employee_role"

    role_id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, primary_key=True)

Alembic migration for it:

op.create_table(
        'employee_role',
        sa.Column('role_id', sa.Integer(), nullable=False),
        sa.Column('employee_id', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('role_id', 'employee_id')
    )

SQL:

CREATE TABLE agent_role (
    role_id INTEGER NOT NULL, 
    employee_id INTEGER NOT NULL, 
    PRIMARY KEY (role_id, employee_id)
);

In terms of relationship, declare it on one side (this should give you role.employees or employee.roles which should return a list):

# this is using Flask-SQLAlchemy with factory pattern, db gives you access to all SQLAlchemy stuff
class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    roles = db.relationship("Role", secondary=employee_role, backref="employee")

Your Role class can be:

# this is using Flask-SQLAlchemy with factory pattern, db gives you access to all SQLAlchemy stuff
class Role(db.Model):
    __tablename__ = "role"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(25), nullable=False, unique=True)
adriaanbd
  • 317
  • 4
  • 12