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:  new
      Priority:  medium                    |  Milestone:  0.8.xx
     Component:  orm                       |   Severity:  major - 1-3 hours
    Resolution:                            |   Keywords:
Progress State:  needs questions answered  |
-------------------------------------------+-------------------------------
Changes (by zzzeek):

 * status_field:  awaiting triage => needs questions answered
 * severity:  no triage selected yet => major - 1-3 hours
 * milestone:   => 0.8.xx


Comment:

 the intent of your code isn't clear.  the issue is not the merge, it's the
 implicit removal of `Related(3, None)` from the collection which is not
 supported.  Here's a simpler reproduction:

 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import sessionmaker, relationship

 engine = create_engine('sqlite:///:memory:', echo=True)
 Session = sessionmaker(bind=engine)
 session = Session()

 Base = declarative_base()

 class Related(Base):
     __tablename__ = 'related'
     parent = Column(Integer, ForeignKey("parent.id"))
     key1 = Column(Integer, primary_key=True, nullable=True)
     key2 = Column(Integer, primary_key=True, nullable=True)

     def __init__(self, key1, key2):
         self.key1, self.key2 = key1, key2

 class Parent(Base):
     __tablename__ = 'parent'
     id = Column(Integer, primary_key=True)
     related = relationship(Related, collection_class=set)

 Base.metadata.create_all(engine)

 # First object
 parent = Parent()
 r1 = Related(3, None)
 parent.related.add(r1)
 session.add(parent)
 session.commit()

 parent.related.remove(r1)
 session.commit()

 }}}

 you may ask, "why is it trying to remove in my example?" because you are
 merging Parent->related((Related(3, None)) which no longer includes
 Related(2, None).   Even though SQLite is allowing this primary key to
 proceed, it is in fact illegal in SQL to have a NULL value in a table-
 bound primary key column (most if not all other backends will reject it in
 some way - PG places an implicit NOT NULL on any PK col, MySQL seems to be
 inserting a zero despite it being missing).  SQLAlchemy allows limited
 support of a NULL for the case where a class is mapped to an OUTER JOIN or
 other composed construct where the columns noted as primary key may
 contain nulls, but they would not ever be the target of an UPDATE.

 So the real issue here is why exactly you need to have a table that stores
 a NULL within a first-class primary key column.   I'm not really sure
 SQLAlchemy should seek to support this pattern.

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