[sqlalchemy] IntegrityError when deleting item mapped with association_proxy

[Felix Schwarz]

I'm using SQLAlchemy's association_proxy to have extensible metadata for my
mapped classes.

So for example I can say:
  user.meta[u'foo'] = u'bar'

However when I try to delete an individual item I get an IntegrityError:
  users_meta.user_id may not be NULL ...

So likely I'm missing some 'cascade' configuration in my schema setup or so
and I'm glad for any pointers.

I attached a minimal script to reproduce the issue. Thank you very much,
fs

PS: I found
http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#sqlite-foreign-keys
but that doesn't seem to make a difference in my case
(sqlite-3.7.13-2.fc18.x86_64).

-- 
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.



from sqlalchemy import Column, ForeignKey, Table, UniqueConstraint, MetaData
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.types import Unicode, Integer
from sqlalchemy.orm import mapper, relation
from sqlalchemy.orm.collections import attribute_mapped_collection

metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, autoincrement=True, primary_key=True),
    Column('name', Unicode(250), nullable=False),
)

users_meta = Table('users_meta', metadata,
    Column('id', Integer, autoincrement=True, primary_key=True),
    Column('user_id', Integer,
        ForeignKey('users.id', onupdate='CASCADE', ondelete='CASCADE'),
        index=True,
        nullable=False),
    Column('key', Unicode(64), nullable=False),
    Column('value', Unicode(250), default=None),

    UniqueConstraint('user_id', 'key'),
    mysql_engine='InnoDB',
    mysql_charset='utf8',
)


class UserMeta(object):
    def __init__(self, key, value):
        self.key = key
        self.value = value

class User(object):
    meta = association_proxy('_meta', 'value', creator=UserMeta)


mapper(
    User, users,
    properties={
        '_meta': relation(
            UserMeta,
            collection_class=attribute_mapped_collection('key'),
            passive_deletes=True,
        ),
    },
)
mapper(UserMeta, users_meta)


from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///:memory:")
metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()
user = User()
user.name = u'foo'
user.meta[u'key'] = u'bar'
session.add(user)
session.commit()

del user.meta[u'key']
session.commit()