CODEWITHSUNDEEP
pythonsqlalchemy
Pierre
Pierre

Reputation: 2752

SQLAlchemy: create a composite primary key made of another composite primary key

In SQLAlchemy, how can I declare a composite primary key which is made from another composite primary key ?

Let's say I have this model:

┌────────┐             ┌───────┐            ┌───────┐
| Person |  ── 1,n ──> |  Pet  | ── 1,n ──> |  Toy  |
├--------┤             ├-------┤            ├-------┤
|   id   |             |  age  |            | color | 
|  name  |             └───────┘            └───────┘
└────────┘

One person can have multiple pets, one pet can have multiple toys. So far, that's simple.

That harder part is that I want Pet and Toy to have composite primary keys:

Where I'm struggling is to use the <Pet primary key> in Toy's composite primary key.

Here is what I tried so far:

class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True, unique=True)
    name = Column(String, unique=True, index=True)


class Pet(Base):
    __tablename__ = 'pet'
    __table_args__ = (PrimaryKeyConstraint('age', 'person_id', name='pet_pk'),)
    age = Column(Integer, default=0)
    person_id = Column(Integer, ForeignKey('person.id'))


class Toy(Base):
    __tablename__ = 'toy'
    __table_args__ = (PrimaryKeyConstraint('color', ???, name='toy_pk'),)
    color = Column(String)

As the primary key of Pet is a composite primary key, how can I use it in another composite primary key ?

In my Toy table, should I add a pet_age column and a person_id column to be able to build the Pet composite key, so I can reference it like this:

(PrimaryKeyConstraint('color', 'pet_age', 'person_id', name='toy_pk')

?

Upvotes: 0

Views: 1109

Answers (1)

Pierre
Pierre

Reputation: 2752

Found it:

  • I need to declare all the Pet composite primary key inside my Toy table
  • create a new composite primary key with all these fields
  • and declare a foreign key constraint with multiple fields:
class Toy(Base):
    __tablename__ = 'toy'
    __table_args__ = (
        PrimaryKeyConstraint('color', 'pet_age', 'person_id', name='toy_pk'),
        ForeignKeyConstraint(['pet_age', 'person_id'], ['pet.age', 'pet.person_id'])
    )
    color = Column(String)
    pet_age = Column(Integer)
    person_id = Column(Integer)

Upvotes: 2

Related Questions