Re: [Sqlalchemy-tickets] [sqlalchemy] #2820: session.merge/nullable primary keys - FlushError: Can't update table using NULL for primary key value

[sqlalchemy <mi...@zz...>]

#2820: session.merge/nullable primary keys - FlushError: Can't update table using
NULL for primary key value
-----------------------------------+-------------------------------
      Reporter:  elsdoerfer        |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:  worksforme        |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------

Comment (by elsdoerfer):

 A quick post-mortem on this, since I finally had to address it:

 - Our pre-SQLAlchemy database had a permission table (user, group,
 permission), where a permission would apply to either a user or a group,
 and the other column was NULL.
 - It used a unique index rather than a primary key.
 - SQLAlchemy does not support a table without a primary key.
 - I wanted to trick SQLAlchemy by using  __mapper_args__ to define a
 primary key, but in truth using a unique key in the database, but during
 session.merge() you run into the FlushError above when using an instance
 with existing permissions relations.

 I've now simply removed the foreign keys from the user and group columns,
 and am storing an integer 0 for the column that does not apply.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2820#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python