Chaining multiple queries in SQL-CodePudding

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;