I'm using postgresql if that matters.
I need to write into 2 different tables when a new user is created and I want to make sure either both queries fail or both of them succeed. If one of them fails, the other should not succeed.
Lets say I've a table named users and another table named houses and whenever a user is created I give them a house.
user table:
userid(int,auto generated) , username(text), password(text)
houses table:
userid(int) , houseid(int)
when a new user is created, I should call following code:
INSERT INTO users (username , password ) VALUES ('a value', 'a value') RETURNING userid;
INSERT INTO houses (userid, houseid ) VALUES ('returned_value' , 'a value');
but if first query succeeds and the second one fails for whatever reason, that means this will create a user without home. How can I chain these 2 queries together so either both of them succeeds or both of them fails.
CodePudding user response:
Transaction is the way you manage what you want to do here, I mean have one global commit only.
With postgresql, the syntax for managing transactions is the following:
BEGIN;
INSERT INTO users (username , password ) VALUES ('a value', 'a value') RETURNING userid;
INSERT INTO houses (userid, houseid ) VALUES ('returned_value' , 'a value');
COMMIT;