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 <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')
> 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
>
>
>
>
>
> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
> Behalf Of Jonathan Vanasco
> Sent: Friday, December 06, 2013 6:07 PM
> To: 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 tosqlalchemy+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.
>
> --
> 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 tosqlalchemy+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.
>
>
> --
> 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 tosqlalchemy+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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
