CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
vipul gangwar
vipul gangwar

Reputation: 1

composite two foreign key in sqlalchemy?

I am using SQLALchemy in flask. And I am confused how to define composite key with two foreign keys. One foreign key from one table and second from another table.

class Store_category_mapping(db.Model):

    __tablename__ = 'store_category_mapping' 
    category_id = db.Column(db.Integer, nullable=False) 
    store_id = db.Column(db.Integer, nullable=False)
    store_name    = db.Column(db.String(50),  nullable=False)

    __table_args__ = (
        db.ForeignKeyConstraint(
            [category_id, store_id],
            [Category_master.category_id, Store_master.store_id]
        ),
        )

It gives this error:

sqlalchemy.exc.ArgumentError: ForeignKeyConstraint on store_category_mapping(category_id, store_id) refers to multiple remote tables: category_master and store_master

Upvotes: 0

Views: 2830

Answers (2)

JCThomas
JCThomas

Reputation: 16

Using ORM style, I got the exception sqlalchemy.exc.ArgumentError: Mapper Mapper[MyTable(mytable)] could not assemble any primary key columns for mapped table 'mytable' with shmee's answer, but it just needed PrimaryKeyConstraint instead of UniqueConstraint, or setting the mapped columns to be primary keys (or both)

from sqlalchemy import UniqueConstraint, PrimaryKeyConstraint, ForeignKey
from sqlalchemy.orm import Mapped, DeclarativeBase, mapped_column

class Base(DeclarativeBase):
    pass

class FornA(Base):
    __tablename__ = 'forn_a'
    id: Mapped[int] = mapped_column(primary_key=True)


class FornB(Base):
    __tablename__ = 'forn_b'
    id: Mapped[int] = mapped_column(primary_key=True)

# using PrimaryKeyContraint
class MyTableA(Base):
    __tablename__ = 'mytable_a'
    __table_args__ = (
        PrimaryKeyConstraint('forn_a_id', 'forn_b_id'),
    )

    forn_a_id: Mapped[int] = mapped_column(ForeignKey(FornA.id))
    forn_b_id: Mapped[int] = mapped_column(ForeignKey(FornB.id))

# setting mapped columns to primary keys with UniqueConstraint
class MyTableB(Base):
    __tablename__ = 'mytable_b'
    __table_args__ = (
        UniqueConstraint('forn_a_id', 'forn_b_id'),
    )

    forn_a_id: Mapped[int] = mapped_column(
        ForeignKey(FornA.id), primary_key=True
    )
    forn_b_id: Mapped[int] = mapped_column(
        ForeignKey(FornB.id), primary_key=True
    )

Upvotes: 0

shmee
shmee

Reputation: 5101

You can't have a composite foreign key that references more than one remote table. Composite foreign keys are a way to ensure integrity when one remote table has a composite primary key.

If you want to make sure that you only ever have one store_category_mapping entry with a given combination of category_id and store_id you could go for a UniqueConstraint instead.

I'm not familiar with flask, but I assume it would look somewhat like this:

class Store_category_mapping(db.Model):

    __tablename__ = 'store_category_mapping' 
    category_id = db.Column(db.Integer, db.ForeignKey(Category_master.category_id), nullable=False) 
    store_id = db.Column(db.Integer, db.ForeignKey(Store_master.store_id), nullable=False)
    store_name    = db.Column(db.String(50),  nullable=False)

    __table_args__ = (
        db.UniqueConstraint('category_id','store_id'),
        )

Upvotes: 3

Related Questions