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. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.