title: SQLAlchemy: Relation table with composite primary key
body: <p>I have a set of tables that look like:</p>
  
  <pre><code>workflows = Table('workflows', Base.metadata,
                    Column('id', Integer, primary_key=True),
                   )
  
  actions = Table('actions', Base.metadata,
                  Column('name', String, primary_key=True),
                  Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True),
                 )
  
  action_dependencies = Table('action_dependencies', Base.metadata,
                              Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True),
                              Column('parent_action', String, ForeignKey(actions.c.name), primary_key=True),
                              Column('child_action', String, ForeignKey(actions.c.name), primary_key=True),
                             )
  </code></pre>
  
  <p>My ORM classes look like:</p>
  
  <pre><code>class Workflow(Base):
      __table__ = workflows
  
      actions = relationship("Action", order_by="Action.name", backref="workflow")
  
  
  class Action(Base):
      __table__ = actions
  
      children = relationship("Action",
                              secondary=action_dependencies,
                              primaryjoin=actions.c.name == action_dependencies.c.parent_action,
                              secondaryjoin=actions.c.name == action_dependencies.c.child_action,
                              backref="parents"
                             )
  </code></pre>
  
  <p>So in my system, each action is uniquely identified by a combination of a workflow id and its name. I'd like each action to have <code>parents</code> and <code>children</code> attribute that refers its parent and child actions. Each action can have multiple parents and children.</p>
  
  <p>The problem occurs when I have a function such as :</p>
  
  <pre><code>def set_parents(session, workflow_id, action_name, parents):
      action = session.query(db.Action).filter(db.Action.workflow_id == workflow.id).filter(db.Action.name == action_name).one()
  
      for parent_name in parents:
          parent = session.query(db.Action).filter(db.Action.workflow_id == workflow.id).filter(db.Action.name == parent_name).one()
          action.parents.append(parent)
  
      session.commit()
  </code></pre>
  
  <p>I get an error like:</p>
  
  <pre><code>IntegrityError: (IntegrityError) action_dependencies.workflow_id may not be NULL u'INSERT INTO action_dependencies (parent_action, child_action) VALUES (?, ?)' (u'directory_creator', u'packing')
  </code></pre>
  
  <p>How do I get the relationship to set the workflow_id correctly?</p>