>> I have 2 tables with one-to-one relation, and I got some unexpected
>> behaviour from sqlalchemy.
>> In attach there is demonstration script and its log.
>>
>> If I try to add object into table "right" for already existing key I
>> expect error on DB level, telling about violation of unique
>> constraint, but sqlalchemy before try to insert new row, set relation
>> key to NULL for already existing object...
>>
>> Is it right?
>> How can I avoid such behavior?
>
> if you are replacing an object reference with another one, then yes SQLA will
> null out the key for the old one first, since you have removed it from its
> parent by replacing it. If you dont want the NULL allowed, the usual
> approach is to have the left_idnr column be NOT NULL - the database then does
> the work of disallowing the operation to proceed. There is a setting for
> passive_deletes, 'all', which disallows the "nulling out" of the foreign key,
> but that only applies to a cascading deletion scenario which is not the case
> here.
>
> For one-to-ones I usually set the foreign key on the child as the primary key
> as well. You can see me asking about this (since a DBA gave me some
> resistance about it recently) here:
> http://stackoverflow.com/questions/2967450/foreign-key-constraints-on-primary-key-columns-issues
I try to change schema(updated example in attach, it drop
AssertionException), to use foreign key as primary key for "child"
table, but it doesn't want to work in this configuration at all.
Please give me link on sqlalchemy documentation, where I can read
about such usage of primary key.
There is only one place in sqlalchemy documentation where I see such
usage of primary key
http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html?highlight=declarative#joined-table-inheritance
but this is not my case.
--
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.
#!/usr/bin/env python
#-*- coding:utf-8 -*-
#import pdb
import os, sys
import time
import logging
#import re
#import errno
#import locale
#import pprint
# mutable inputs
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
decl = declarative_base()
class left(decl):
__tablename__ = 'left'
idnr = Column(Integer, primary_key=True)
data = Column(String(32), nullable=False)
rel = orm.relation('right', uselist=False,
backref=orm.backref('left'),
passive_updates=True, passive_deletes=True, lazy=True, cascade='all')
class right(decl):
__tablename__ = 'right'
#idnr = Column(Integer, primary_key=True)
left_idnr = Column(Integer, ForeignKey(left.idnr, onupdate='CASCADE', ondelete='CASCADE'), primary_key=True, unique=True)
data = Column(String(32), nullable=False)
def main():
logging.basicConfig()
log = logging.getLogger('sqlalchemy.engine')
log.setLevel(logging.DEBUG)
eng = create_engine('sqlite://')
sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False)
decl.metadata.create_all(eng)
ses = sm()
l = left()
l.data = 'abc'
ses.add(l)
r = right()
r.left = l
r.data = 'cde'
ses.add(r)
ses.commit()
l_idnr = l.idnr
ses.close()
ses = sm()
l = ses.query(left).get(l_idnr)
r2 = right()
r2.left = l
r2.data = '012'
ses.flush()
ses.commit()
if __name__ == '__main__':
main()
