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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
