SQLAlchemy: Relation table with composite primary key

Question

I have a set of tables that look like:

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),
                           )

My ORM classes look like:

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"
                           )

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 parents and children attribute that refers its parent and child actions. Each action can have multiple parents and children.

The problem occurs when I have a function such as :

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()

I get an error like:

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')

How do I get the relationship to set the workflow_id correctly?

Why do you need to have workflow_id in the action_dependencies table? — van
– van, Commented May 10, 2012 at 9:06
Because the primary key for an action is a composite of its name and workflow_id. If the workflow_id was not in action_dependencies, there'd be no way to tell which workflow's actions the dependency was referring to. — Kamil Kisiel
– Kamil Kisiel, Commented May 10, 2012 at 15:10
Please note that your parents/children relationships should also include the workflow_id in the primaryjoin and secondary conditions, or else you will get these for all worklows. — van
– van, Commented May 10, 2012 at 15:33

van · Accepted Answer · 2012-05-11 11:54:53Z

See below working code. The key points are those I mentioned in the comments:

proper composite ForeignKeys
correct relationship configuration using the FKs

Code:

workflows = Table('workflows', Base.metadata,
                  Column('id', Integer, primary_key=True),
                 )

actions = Table('actions', Base.metadata,
                Column('workflow_id', Integer, ForeignKey(workflows.c.id), primary_key=True),
                Column('name', String, 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),
                            ForeignKeyConstraint(['workflow_id', 'parent_action'], ['actions.workflow_id', 'actions.name']),
                            ForeignKeyConstraint(['workflow_id', 'child_action'], ['actions.workflow_id', 'actions.name']),
                           )
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=and_(actions.c.name == action_dependencies.c.parent_action,
                                actions.c.workflow_id == action_dependencies.c.workflow_id),
                            secondaryjoin=and_(actions.c.name == action_dependencies.c.child_action,
                                actions.c.workflow_id == action_dependencies.c.workflow_id),
                            backref="parents"
                           )

# create db schema
Base.metadata.create_all(engine)

# create entities
w_1 = Workflow()
w_2 = Workflow()
a_11 = Action(name="ac-11", workflow=w_1)
a_12 = Action(name="ac-12", workflow=w_1)
a_21 = Action(name="ac-21", workflow=w_2)
a_22 = Action(name="ac-22", workflow=w_2)
session.add(w_1)
session.add(w_2)
a_22.parents.append(a_21)
session.commit()
session.expunge_all()
print '-'*80

# helper functions
def get_workflow(id):
    return session.query(Workflow).get(id)
def get_action(name):
    return session.query(Action).filter_by(name=name).one()

# test another OK
a_11 = get_action("ac-11")
a_12 = get_action("ac-12")
a_11.children.append(a_12)
session.commit()
session.expunge_all()
print '-'*80

# test KO (THIS SHOULD FAIL VIOLATING FK-constraint)
a_11 = get_action("ac-11")
a_22 = get_action("ac-22")
a_11.children.append(a_22)
session.commit()
session.expunge_all()
print '-'*80

Keith · Accepted Answer · 2012-05-10 04:43:22Z

0

I don't think it's correct to have the primary key a foreign key. How does that work?

But to make composite constraint, a key that is "unique together", use this in the table definition:

UniqueConstraint(u"name", u"workflow_id"),

But if you really want it to be the primary key also you can use this:

PrimaryKeyConstraint(*columns, **kw)

http://docs.sqlalchemy.org/en/latest/core/schema.html#sqlalchemy.schema.PrimaryKeyConstraint

answered May 10, 2012 at 4:43

Keith

43.2k11 gold badges62 silver badges77 bronze badges

1 Comment

SingleNegationElimination Over a year ago

there's nothing wrong with foreign key constraints on primary keys; this is a typical way to get a 'one-to-one' relationship, to map subclasses to the database, or to have attributes that can all be "null together"

Collectives™ on Stack Overflow

SQLAlchemy: Relation table with composite primary key

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related