SqlAlchemy 0.7.10 silently omits duplicate entries in many to many relation http://stackoverflow.com/questions/18401270/sqlalchemy-0-7-10-silently-omits-duplicate-entries-in-many-to-many-relation

Omits duplicate

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey, String, create_engine
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine("sqlite:///:memory:", echo="debug")
Session = sessionmaker(bind=engine)
BASE = declarative_base()

session = Session()

class Node(BASE):
    __tablename__ = "nodes"
    id = Column(Integer, primary_key=True)

class ZoneNodeAssociation(BASE):
    __tablename__ = "zone_node_association"
    node_id = Column(Integer, ForeignKey('nodes.id'),
                     primary_key=True, nullable=False)
    zone_id = Column(Integer, ForeignKey('zones.id'),
                     primary_key=True, nullable=False)    

class Zone(BASE):
    __tablename__ = "zones"
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    name = Column(String(255), unique=True)
    nodes = relationship("Node",
                         secondary="zone_node_association",
                         backref="zones",)

BASE.metadata.create_all(engine)

node = Node()
zone = Zone()

session.add(node)
session.add(zone)

zone.nodes.append(node)
session.flush()

zone.nodes.append(node)
session.commit()

Shows an integrity error as expected

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey, String, create_engine
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine("sqlite:///:memory:", echo="debug")
Session = sessionmaker(bind=engine)
BASE = declarative_base()

session = Session()

class Node(BASE):
    __tablename__ = "nodes"
    id = Column(Integer, primary_key=True)

class ZoneNodeAssociation(BASE):
    __tablename__ = "zone_node_association"
    node_id = Column(Integer, ForeignKey('nodes.id'),
                     primary_key=True, nullable=False)
    zone_id = Column(Integer, ForeignKey('zones.id'),
                     primary_key=True, nullable=False)    

class Zone(BASE):
    __tablename__ = "zones"
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    name = Column(String(255), unique=True)
    nodes = relationship("Node",
                         secondary="zone_node_association",
                         backref="zones",)

BASE.metadata.create_all(engine)

node = Node()
zone = Zone()

session.add(node)
session.add(zone)

zone.nodes.append(node)
# session.flush()

zone.nodes.append(node)
session.commit()