Hey all, The below code establishes 3 tables (house, dog, owner) and a mapper table to associate owners and dogs (friendships).
When I use either MySQL (5.0.51) or SQLite (3.4.2) as the backend, this code works correctly. However when I use Postgres (either 8.2.7 or 8.3.1) I get the following integrity error: sqlalchemy.exceptions.IntegrityError: (IntegrityError) update or delete on table "dog" violates foreign key constraint "friendship_dog_id_fkey" on table "friendship" DETAIL: Key (id)=(1) is still referenced from table "friendship". 'DELETE FROM dog WHERE dog.id = %(id)s' [{'id': 1}, {'id': 2}] Interestingly, if I change the "dog" relation on the "Friendship" mapper to not have a cascade rule, the delete works correctly - however that means dog records won't get deleted if they get orphaned. Just for reference, I run this script as follows: # to build the database % ./db.py --build # to find out the id's I want to be watching using the db cli % ./db.py % ./db.py --del Cheers Dave --- #!/usr/bin/python import sys import sqlalchemy as sa import sqlalchemy.orm session = sa.orm.scoped_session( sa.orm.sessionmaker(autoflush=False, transactional=True) ) mapper = session.mapper metadata = sa.MetaData() houseTable = sa.Table( 'house', metadata, sa.Column('id', sa.Integer, primary_key=True), ) ownerTable = sa.Table( 'owner', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('house_id', sa.Integer, sa.ForeignKey('house.id')), ) dogTable = sa.Table( 'dog', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('house_id', sa.Integer, sa.ForeignKey('house.id')), ) friendshipTable = sa.Table( 'friendship', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('owner_id', sa.Integer, sa.ForeignKey('owner.id')), sa.Column('dog_id', sa.Integer, sa.ForeignKey('dog.id')), ) class House(object): pass class Owner(object): pass class Dog(object): pass class Friendship(object): pass mapper( House, houseTable, properties = { "owners" : sa.orm.relation( Owner, cascade="delete-orphan" ), "dogs" : sa.orm.relation( Dog, cascade="delete-orphan" ), }, ) mapper( Owner, ownerTable, properties = { "friendships" : sa.orm.relation( Friendship, cascade="delete" ), }, ) mapper( Friendship, friendshipTable, properties = { "dog" : sa.orm.relation( Dog, uselist=False, cascade="all, delete-orphan" ), }, ) mapper(Dog, dogTable) if __name__ == "__main__": from optparse import OptionParser parser = OptionParser() parser.add_option("--build", dest="build", action="store_true", default=False) parser.add_option("--del", dest="delete", action="store_true", default=False) (options, args) = parser.parse_args() engine = sa.create_engine( "postgres://dave:[EMAIL PROTECTED]:5432/satest", #"mysql://dave:[EMAIL PROTECTED]:3306/satest", #"sqlite:///:memory:", strategy="threadlocal", echo=True ) metadata.bind = engine session.configure(bind=engine) if options.build: print "Creating tables" metadata.create_all() print "Seeding database" for i in range(10): House() session.flush() for house in sa.orm.Query(House).all(): for i in range(2): owner = Owner() house.owners.append(owner) session.flush() for house in sa.orm.Query(House).all(): for i in range(2): dog = Dog() house.dogs.append(dog) session.flush() for owner in sa.orm.Query(Owner).all(): for dog in sa.orm.Query(Dog).filter_by(house_id = owner.house_id).all(): friendship = Friendship() friendship.dog = dog owner.friendships.append(friendship) session.commit() print "HERE HERE HERE =================================" owner = sa.orm.Query(Owner).first() for f in owner.friendships: print "FRIENDSHIP: %s || DOG: %s" % (f.id, f.dog.id) if options.delete: print "HERE HERE HERE =================================" session.delete(owner) session.flush() session.commit() --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---