I had misunderstood the documentation on relationships and then tied
myself in a knot, I thought that relationship() defined a strictly
parent to child relationship and that all the other parameters were
from the parents point of view.
My runs and is a lot cleaner!
Thank you for helping me,
BEN
On Sep 15, 3:20 pm, Conor <conor.edward.da...@gmail.com> wrote:
> On 09/15/2010 05:04 PM, BenH wrote:
>
> > Hi,
>
> > I'm using SqlAlchemy 0.6.3 and PostgreSQL 8.4 and I'm trying to setup
> > a cascading delete between several levels of tables. The problem seems
> > to be that I can't have a relationship with cascade="all" and a column
> > with ForeignKey that has nullable=False.
>
> Your "cascade" clause is on the wrong side of the relationship. It means
> you want to cascade TO the remote object, not FROM the remote object.
>
>
>
> > Here is my example:
>
> > from sqlalchemy import create_engine, Table, Column, Integer, String,
> > MetaData, ForeignKey
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import scoped_session, sessionmaker, relationship
>
> > engine = create_engine('postgresql://tsq:passt%qw...@localhost:5432/
> > ce_cascade_test', echo=True)
>
> > Session = scoped_session(sessionmaker())
> > Base = declarative_base()
>
> > Session.configure(bind=engine)
>
> > s = Session()
>
> > class User(Base):
> > __tablename__ = "users"
>
> > id = Column(Integer, primary_key=True)
> > name = Column(String)
>
> > device = relationship("Device", uselist=False)
>
> This should be:
>
> device = relationship("Device", cascade="all", passive_deletes=True,
> uselist=False)
>
> Also, including "uselist=False" indicates that the User-Device
> relationship is one-to-one. The lack of a unique constraint on
> "devices.user_id" suggests a one-to-many relationship.
>
>
>
> > @classmethod
> > def create(cls, user_name, device_name, manufacturer):
> > new_user = User()
> > new_user.name = user_name
>
> > new_user.device = Device.create(device_name)
>
> > return new_user
>
> > class Device(Base):
> > __tablename__ = "devices"
>
> > id = Column(Integer, primary_key=True)
> > name = Column(String)
> > user_id = Column(Integer,
> > ForeignKey('users.id'),
> > nullable=False)
>
> > manufacturer_id = Column(Integer,
> > ForeignKey('manufacturers.id'),
> > nullable=False)
>
> > user = relationship("User",
> > uselist=False,
> > cascade="all")
>
> This should be:
>
> user = relationship("User")
>
> Adding "uselist=False" here is redundant, since that is the default for
> the side which contains the foreign key. Also, by including
> 'cascade="all"', you are telling SQLAlchemy to implicitly delete the
> user when the device is deleted. This is probably not what you want.
>
>
>
> > @classmethod
> > def create(cls, name):
> > new_device = Device()
> > new_device.name = name
>
> > return new_device
>
> > Base.metadata.create_all(engine)
>
> > user = User.create("bob", "iphone")
>
> > s.add(user)
> > s.commit()
>
> > s.delete(user)
> > s.commit()
>
> > If I run this then I get an Integrity error during the delete (because
> > it is updating the user_id to null before deleting it). I would like
> > to keep the nullable=False on the ForeignKey.
>
> > I've tried adding ondelete="CASCADE" to the ForeignKey and adding
> > passive_deletes=True to the relationship but it always throw the same
> > Integrity error.
>
> > I know I'm missing something but I can't find it in the docs, what am
> > I missing?
>
> > Thanks for your help,
>
> > BEN
>
> -Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
