SQL rollback of multiple inserts involving constraints

[Loris Bennett]

Jacob Kruger <jacob.kruger.work at gmail.com> writes:

> Think performing a session/transaction flush after the first two
> inserts should offer the workaround before you've committed all
> transaction actions to the database finally:
>
> https://medium.com/@oba2311/sqlalchemy-whats-the-difference-between-a-flush-and-commit-baec6c2410a9
>
>
> HTH

Yes, thank you, it does.  I hadn't been aware of 'flush'.

> Jacob Kruger
> +2782 413 4791
> "Resistance is futile!...Acceptance is versatile..."
>
>
> On 2023/11/10 11:15, Loris Bennett via Python-list wrote:
>> 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
>>
>
-- 
Dr. Loris Bennett (Herr/Mr)
ZEDAT, Freie Universität Berlin