Robert Christopher Robert Christopher - 1 month ago 7
Python Question

Simple SQLAlchemy hiearchical inheritance model

I am modeling a simple hierarchical database structure. My model is designed as follows:

class ChatMessage(Base):
__tablename__ = 'chat_message'
sender_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
receiver_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
text = Column(String(50))


class User(Base):
__tablename__ = 'user'


id = Column(Integer, primary_key=True)
phone_number = Column(PhoneNumberType())
type = Column(String(50))

__mapper_args__ = {
'polymorphic_identity': 'user',
'polymorphic_on': type
}

sent_messages = relationship(
'ChatMessage',
foreign_keys='ChatMessage.sender_id',
backref='sending_user'
)

received_messages = relationship(
'ChatMessage',
foreign_keys='ChatMessage.sender_id',
backref='sending_user'
)


class InvitedUser(TBHUser):
__tablename__ = 'invited_user'
id = Column(Integer, ForeignKey('user.id'), primary_key=True)

__mapper_args__ = {
'polymorphic_identity': 'invited_user',
}


class VerifiedUser(TBHUser):
__tablename__ = 'verified_user'
id = Column(Integer, ForeignKey('user.id'), primary_key=True)

__mapper_args__ = {
'polymorphic_identity': 'verified_user',
}


I'll give a brief breakdown of some structural business rules present in my application. A single user is able to chat with anyone in their contacts. If a user messages a friend who is not registered in the app, the chat message will be sent and the receiving user will receive an invitation to download the app. Invited users are stored inside the 'InvitedUser' table. After an invited user signs up, the 'InvitedUser' instance will be deleted and will be replaced with a 'VerifiedUser' instance. The purpose of the 'InvitedUser' entity is to persist received chat messages prior to signup.

While deleting an InvitedUser from the database, I am receiving the error below.

AssertionError: Dependency rule tried to blank-out primary key column 'chat_message.receiver_id' on instance '<ChatMessage at 0x1044a9cf8>'


It appears that SQLAlchemy is not allowing the deletion of an InvitedUser in order to preserve integrity of the ChatMessages received by the user.
A simple solution would be to have one user class and potentially a 'status' attribute, however, that did not seem optimal.

Is there a specific way to tell SQLAlchemy to transfer an 'InvitedUser' to 'VerifiedUser'? Possibly my models are overcomplicated and there is a different way of solving this problem.

Thanks,
Rob.

Answer

As you mentioned, the reason you're being prevented from deleting your InvitedUser is because they have been the recipient of one or more ChatMessage, and if you deleted your user then you'd be left with one or more messages pointing to a user that does not exist. If you're convinced you definitely want two different user classes, you'll need to:

  • create a new VerifiedUser corresponding to your InvitedUser
  • find all ChatMessage where the receiver_id matches the ID of your InvitedUser
  • update all such messages to point to your newly-created VerifiedUser

at this point all references to your InvitedUser entity should be gone, and you should be able to safely delete the InvitedUser

Note

You should strongly consider whether you genuinely need two separate user classes. Note that the definitions you have provided are identical apart from the polymorphic_identity, which you could model equally well with a status column, as mentioned in comments on your post. This would also have the benefit of being conceptually much more simple, as well as neatly side stepping the problem you're currently facing.

Comments