Well, users are not expected to run sql query like DELETE FROM directly, but they have a GUI that allows them to CRUD tags. The GUI is working with data through sqlalchemy ORM.
Anyway - passive_deletes='all' does the trick. Many thanks! From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Sunday, December 08, 2013 1:46 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Many2many, referential integrity and introspection On Dec 7, 2013, at 6:18 PM, Alexey Vihorev <viho...@gmail.com> wrote: In my case, table 'tags' is editable by users, so I want to avoid them deleting a tag that's in use by any product. deleting..using a SQL DELETE statement directly? If you use real foreign keys then the database will prevent deletion of rows that are referred to by rows in the many-to-many table. If you're referring ORM relationship code, the "delete the rows in the secondary table" feature can be disabled using "passive_deletes='all'" - in which case you'll then get the above mentioned integrity violation generated from the database. E.g.: class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) name = Column(Unicode(20)) products = relationship("Product", secondary=product_tags, passive_deletes="all") class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) name = Column(Unicode(20)) tags = relationship("Tag", secondary=product_tags, passive_deletes="all") program output is then: sqlalchemy.exc.IntegrityError: (IntegrityError) update or delete on table "tags" violates foreign key constraint "product_tags_tag_id_fkey" on table "product_tags" DETAIL: Key (id)=(1) is still referenced from table "product_tags". 'DELETE FROM tags WHERE tags.id = %(id)s' {'id': 1} As it is now, they can delete the tag and the system just erases all traces of it from all products. From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Saturday, December 07, 2013 11:51 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Many2many, referential integrity and introspection On Dec 7, 2013, at 2:22 PM, Alexey Vihorev < <mailto:viho...@gmail.com> viho...@gmail.com> wrote: Here is the full code: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import * from sqlalchemy.orm import relationship, sessionmaker Base = declarative_base() product_tags = Table( 'product_tags', Base.metadata, Column('product_id', Integer, ForeignKey('products.id')), Column('tag_id', Integer, ForeignKey('tags.id')) ) class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) name = Column(Unicode(20)) products = relationship("Product", secondary=product_tags) class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) name = Column(Unicode(20)) tags = relationship("Tag", secondary=product_tags) engine = create_engine(' <postgresql://user:password@localhost/example'> postgresql://user:password@localhost/example') Session = sessionmaker(bind=engine) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) p1 = Product(name = 'Milk') p2 = Product(name = 'Water') tag1 = Tag(name='Liquid') p1.tags.append(tag1) p2.tags.append(tag1) s = Session() s.add_all([p1, p2, tag1]) s.commit() s.close() s = Session() s.delete(s.query(Tag).first()) s.commit() s.close() After it runs, the product_tags table is completely empty. that's the right behavior, if you remove the Tag, then the row in "tags" is gone. It would be a referential integrity violation if rows remained in product_tags. if you want to remove rows from product_tags specifically, just alter the collections as needed: p1.tags.remove(tag1) this is a common misunderstanding so a documentation section exists for it: <http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#deleting-rows- from-the-many-to-many-table> http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#deleting-rows-f rom-the-many-to-many-table From: <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com [ <mailto:sqlalchemy@googlegroups.com> mailto:sqlalchemy@googlegroups.com] On Behalf Of Jonathan Vanasco Sent: Friday, December 06, 2013 6:07 PM To: <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Many2many, referential integrity and introspection can you share your existing schema for these relations ? -- 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/groups/opt_out> https://groups.google.com/groups/opt_out. -- 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/groups/opt_out> https://groups.google.com/groups/opt_out. -- 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/groups/opt_out> https://groups.google.com/groups/opt_out. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
