Hey guys,
I encountered a very strange problem with SQLAlchemy. I have a model
that is self-referencing (adjacency list relationship). I simply
copied the model (Node) from the SQLAlchemy tutorial. Here is the
model's code:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('nodes.id'))
parent = Column(Unicode(50))
children = relationship('Node',
cascade="all", #tried to remove this
backref=backref("parent", remote_side='Node.id'),
collection_class=attribute_mapped_collection('data'),
#tried to remove this as well
)
I reproduced the problem within my controllers, but I also ran this
test (after fully loading my environment of course):
parent = Node()
parent.id = 1
parent.parent_id = None
parent.name = 'parent'
Session.add(parent)
child = Node()
child.id = 20
child.parent_id = 1
child.name = 'child'
Session.add(child)
Session.commit()
The above code works just fine (the changes are successfully committed
and reflected in the DB).
The problem arises when I change the `parent` node's id to 0 (and the
`child`'s parent_id to 0 accordingly). Then, I get the following
exception:
......
File "C:\Python26\Lib\site-packages\MySQLdb\connections.py", line
36, in defaulterrorhandler
raise errorclass, errorvalue
sqlalchemy.exc.IntegrityError: (IntegrityError) (1452, 'Cannot add
or update a child row: a
foreign key constraint fails (`db`.`nodes`, CONSTRAINT
`nodes_ibfk_1` FOREIGN KEY
(`parent_id`) REFERENCES `nodes` (`id`))') 'INSERT INTO nodes (id,
parent_id, name) VALUES
(%s, %s, %s)' (20, 0, 'child')
Surprisingly, changing this value (the `node`'s id and the `child`'s
parent_id) to _anything_ but 0 (-5, 1 and 150) makes the error go
away.
Am I missing something obvious? Is it not possible to assign 0 to a
self-referencing Integer id column?
Thanks!
--
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.
