I have a many to one relationship between the Userownedshare
table and the Share
table.
When I delete a Userownedshare
entry from the database I get the following error: AssertionError: Dependency rule tried to blank-out primary key column 'share.ticker'
This makes sense as the ticker
field in Userownedshare is a Foreign Key in the Share
table. However I cannot work out how to fix this error. I think that I want to set up a cascading deletion when a Share
entry is orphaned but I cannot work out how to do this, I have read the documentation but I just end up with different types of errors so I think I am missing something trivial.
Hope someone can help, thanks!
Here is my code:
class Userownedshare(db.Model):
id = db.Column(db.Integer, primary_key=True)
ticker = db.Column(db.String(20), db.ForeignKey('share.ticker'))
user = db.Column(db.String, db.ForeignKey('user.username'))
quantity = db.Column(db.Integer, nullable=False)
dividends = db.Column(db.Float, server_default="0.0")
triggerlevel = db.Column(db.Integer)
smsalert = db.Column(db.Boolean)
emailalert = db.Column(db.Boolean)
portfolioid = db.Column(db.String(50))
name = db.relationship('Share', backref='userownedshare' , foreign_keys=[ticker])
class Share(db.Model):
id = db.Column(db.Integer)
name = db.Column(db.String(50), nullable=False)
ticker = db.Column(db.String(50), db.ForeignKey('userownedshare.ticker'), primary_key=True)
tickermatch = db.relationship('Userownedshare', backref='share', foreign_keys=[ticker])
OK, so after some trial and error I worked it out.
I needed to add cascade="all, delete-orphan",
to the tickermatch
relationship in the Share
class.
But I also needed to add lazy="joined"
to the name
relationship in the Userownedshare
class
class Userownedshare(db.Model):
id = db.Column(db.Integer, primary_key=True)
ticker = db.Column(db.String(20), db.ForeignKey('share.ticker'))
user = db.Column(db.String, db.ForeignKey('user.username'))
quantity = db.Column(db.Integer, nullable=False)
dividends = db.Column(db.Float, server_default="0.0")
triggerlevel = db.Column(db.Integer)
smsalert = db.Column(db.Boolean)
emailalert = db.Column(db.Boolean)
portfolioid = db.Column(db.String(50))
name = db.relationship('Share', backref='userownedshare', foreign_keys=[ticker], lazy="joined")
class Share(db.Model):
id = db.Column(db.Integer)
name = db.Column(db.String(50), nullable=False)
ticker = db.Column(db.String(50), db.ForeignKey('userownedshare.ticker'), primary_key=True)
tickermatch = db.relationship('Userownedshare', backref='share', cascade="all, delete-orphan", lazy="joined")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With