CODEWITHSUNDEEP
pythonsqlsqlalchemy
Alina
Alina

Reputation: 3

SQLAlchemy relationships conflict

I have a problem with forming relationships in a table.

I need to put the pets in boxes. One box - one pet. Pets are divided into two tables according to their characteristics.

How can I put the dog_id(Dogs) or cat_id(Cats) to the pet_id(Boxes)?

I tried the following:

class Boxes():
    __tablename__ = 'Boxes table'

    box_id = Column('Box ID', NVARCHAR(5), primary_key=True)
    pet_id = Column('Pet ID', ForeignKey('Dogs table.DOG ID'), ForeignKey('Cats table.CAT ID'))
    
    pet_cat = relationship('Cats')
    pet_dog = relationship('Dogs')
    
class Dogs():
    __tablename__ = 'Dogs table'
    
    dog_id = Column('DOG ID', NVARCHAR(10), primary_key=True)
    dog_characteristics = Column('Dog Characteristics', NVARCHAR(20))

class Cats():
    __tablename__ = 'Cats table'
    
    cat_id = Column('CAT ID', NVARCHAR(10), primary_key=True)
    cat_characteristics = Column('Cat Characteristics', NVARCHAR(50))

But there is a conflict:

relationship 'Boxes.pet_cat' will copy column Cats table.CAT ID to column Boxes table.Pet ID, which conflicts with relationship(s): 'Boxes.pet_dog'

How should I correctly establish a relationship? Thanks

Upvotes: 0

Views: 731

Answers (2)

Hashir Irfan
Hashir Irfan

Reputation: 334

Your structure looks polymorphic that you have a pet class and sub classes dog and cat. Define the polymorphic type in mapper_args. Add foreign key in Boxes class. Create relationship with pet instead of Dog or Cat. Pet will help you to have Dog or Cat attributes according to the pet_type.

from sqlalchemy.orm import backref, relationship
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, String

class Boxes():
    __tablename__ = 'Boxes table'

    box_id = Column('Box ID', NVARCHAR(5), primary_key=True)
    pet_id = Column(Integer, ForeignKey('pet.id'))
    pet = relationship("Pet", backref=backref("boxes", cascade="all, delete-orphan", lazy=True))

class Pet():
    __tablename__ = 'pet'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    pet_type = Column(String)

    __mapper_args__ = {"polymorphic_identity": "pet", "polymorphic_on": pet_type}

class Dogs(Pet):
    __tablename__ = 'Dogs table'
    
    id = Column(Integer, ForeignKey("pet.id"), primary_key=True)
    dog_characteristics = Column('Dog Characteristics', NVARCHAR(20))
    __mapper_args__ = {"polymorphic_identity": "pet_dog"}

class Cats(Pet):
    __tablename__ = 'Cats table'
    
    id = Column(Integer, ForeignKey("pet.id"), primary_key=True)
    cat_characteristics = Column('Cat Characteristics', NVARCHAR(50))
    __mapper_args__ = {"polymorphic_identity": "pet_cat"}

Upvotes: 2

persian-theme
persian-theme

Reputation: 6638

You cannot assign a key to two tables at the same time.

For this case, design a table for animals and give a type field to the table and this main key of this table as the external key to the box table.

class Animals():
    __tablename__ = 'Animals table'
    
    animal_id = Column('Animal ID', NVARCHAR(10), primary_key=True)
    animal_characteristics = Column('Animal Characteristics', NVARCHAR(20))
    animal_type = Column('Animal Type', NVARCHAR(20))

and Boxes

class Boxes():
    __tablename__ = 'Boxes table'

    box_id = Column('Box ID', NVARCHAR(5), primary_key=True)
    pet_id = Column('Pet ID', ForeignKey('Animals table.Animal ID'))
    
    pet_animal = relationship('Animals')

Upvotes: 0

Related Questions