SQLAlchemy cascading deletes to many to many table

user_1230911 0
Viewed 315

I have a situation where i want to upon deletion of a Key (Parent) delete all it's Values (children), and the related rows in a MTM association between Children and Foo. The models are as follows (some attribs removed):


class Key(Model):
   ...
   values = relationship('Value', back_populates='key', cascade='all, delete-orphan')

class Value(Model):
   
   value = Column(Text, nullable=False)
   key = relationship("Key", back_populates="values")
   key_id = Column(Integer, ForeignKey("keys.id"))


class Foo(Model):

   ...
   taglist = db.relationship("Value", 
                               order_by="desc(Value.created)", 
                               secondary=association_table, 
                               lazy='dynamic')

And the association_table is just a classic FK<-->FK table:

Table("association_table", 
   Model.metadata,
   Column("foo.id", Integer, ForeignKey("foos.id"),
   Column("value.id", Integer, ForeignKey("values.id"),)

With this setup, using postgres, when Im trying to delete a Key, I get the following: (psycopg2.errors.ForeignKeyViolation) update or delete on table "values" violates foreign key constraint "association_table_value_id_fkey" on table "association_table"

Now, reading through the https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#relationships-many-to-many-deletion documentation I've tried the examples there which mostly seem to say put an ondelete="CASCADE" on your association table, however this results in the same issue.

Is there a way I can do what I want, e.g. "unlink" the foo objects from the MTM table (without deleting them) ?

Thanks in advance for any help

flask sqlalchemy postgresql python
1 Answers

If you are using postgres, it would be useful to know wich version you are running. There are some similar issues which might be related if you are using a version lower than 12.2.

However, from your code, where did you try ondelete="CASCADE"? This is working for me:

association = Table("association_table", 
   PkModel.metadata,
   Column("foo.id", ForeignKey("foos.id")),
   Column("value.id", ForeignKey("values.id", ondelete="CASCADE"))
   )


class Key(PkModel):
    __tablename__ = "keys" 
    values = relationship('Value', back_populates='key', cascade='all, delete-orphan')


class Value(PkModel):
    __tablename__ = "values"
    created = Column(DateTime, nullable=False, default=dt.utcnow)
    value = Column(Text, nullable=False)
    key = relationship("Key", back_populates="values")
    key_id = Column(ForeignKey("keys.id"))


class Foo(PkModel):
    __tablename__ = "foos"
    taglist = relationship(
       "Value", 
        order_by="desc(Value.created)", 
        secondary=association, 
        lazy='dynamic'
)

Then I can run (using postgres 12.2):

>>>f1 = models.Foo.create()
>>>f1.taglist.all()
[]
>>>k1 = models.Key.create(values=[models.Value(value="bar")])
>>>k1.values
[<Value 54a7c726-acc9...b8c87e590>]
>>>models.Value.query.all()
[<Value 54a7c726-acc9...b8c87e590>]

>>>f1.update(taglist=[v1])
<Foo 8a1b6701-384e-4bc0-8e29-9ac4e64a4fdf>
>>>f1 = models.Foo.query.all()[0]
>>>f1.taglist.all()
[<Value 54a7c726-acc9...b8c87e590>]

>>>k1.delete()
None
>>>f1.taglist.all()
[]

>>>models.Key.query.all()
[]
>>>models.Value.query.all()
[]
>>>models.Foo.query.all()
[<Foo 8a1b6701-384e-4...4e64a4fdf>]

I tried after without ondelete="CASCADE" and I could reproduce your error.

ForeignKeyViolation: update or delete on table "values" violates foreign key constraint "association_table_value.id_fkey" on table "association_table"

From your code, for future readers, note the following:

  • Missing the __tablename__, I suppose from your code is set adding 's'.
  • On the association table, missing a ) on Column("foo.id").
  • Value does not have a mapped column named 'created'.
user_129799930
Related