SqlAlchemy: UPDATE and INSERT order wrong with foreign key to self

I have a table which has a foreign key relationship with itself (on a compound primary key)

E.g. something like the following:

CREATE TABLE graph (
  start_id character varying(50) NOT NULL,
  end_id character varying(50) NOT NULL,
  weight integer,
  other_start_id character varying(50),
  other_end_id character varying(50),
  CONSTRAINT graph_pkey PRIMARY KEY (start_id, end_id),
  CONSTRAINT graph_other FOREIGN KEY (other_start_id, other_end_id)
     REFERENCES graph (start_id, end_id) MATCH SIMPLE
     ON UPDATE SET NULL ON DELETE SET NULL,
)

In SqlAlchemy, I create a new (pending) graph object 'new_obj', and assign it to the other attribute of an existing persistent object, i.e.:

exist_obj.other = new_obj

When I commit the session, SqlAlchemy issues the UPDATE on the existing object before it issues the INSERT to create the new_obj. My database rightly complains on the UPDATE that it can't find the foreign key graph_other (as new_obj hasn't been inserted yet).

I thought SqlAlchemy was supposed to be smart about the ordering of SQL? I'm using version 0.5.4

Is there a way of manually ordering the operations?

ansaurus

tags:

views:

answers:

SqlAlchemy: UPDATE and INSERT order wrong with foreign key to self

related questions