SQL rollback of multiple inserts involving constraints

[Loris Bennett]

Hi,

In my MariaDB database I have a table 'people' with 'uid' as the primary
key and a table 'groups' with 'gid' as the primary key.  I have a third
table 'memberships' with 'uid' and 'gid' being the primary key and the
constraint that values for 'uid' and 'gid' exist in the tables 'people'
and 'groups', respectively.  I am using SQLAlchemy and writing a method
to setup a membership for a new person in a new group.

I had assumed that I should be able to perform all three inserts
(person, group, membership) with a single transaction and then rollback
if there is a problem.  However, the problem is that if the both the
insert into 'people' and that into 'groups' are not first committed, the
constraint on the insertion of the membership fails.

What am I doing wrong?

Apologies if this is actually an SQL question rather than something
related to SQLAlchemy.

Cheers,

Loris

-- 
This signature is currently under constuction.