CODEWITHSUNDEEP
postgresqltransactionsforeign-keys
pedrorezende
pedrorezende

Reputation: 288

use the returned value of the query as a foreign key without starting a new transaction or dropping foreign key constraint

I'm inserting a value in table A, that has a serial type as primary key.

Using the returned value of the query as a foreign key of table B I get:

ERROR: insert or update on table "tb_midia_pessoa" violates foreign key constraint "tb_midia_pessoa_id_pessoa_fkey"
DETAIL: Key (id_pessoa)=(30) is not present in table "tb_pessoa"

How can I use the returned value of the query as a foreign key of table B without starting a new transaction or dropping my foreign key constraint?

Upvotes: 14

Views: 14867

Answers (3)

SimonSimCity
SimonSimCity

Reputation: 6572

The scenario described in the question shouldn't raise an error any more.

In PostgreSQL v14 it is possible to insert a record into table a and use its id as reference in table b. If you're on at least v14 and see the error described, something else is wrong; it's transactions vs foreign keys for you.

The following runs without error, which proves the problem in the question does not exist any more in the scenario in the question:

create table tab_a (
  id int primary key generated always as identity,
  foo text
);

create table tab_b (
  id int primary key generated always as identity,
  a_id int NOT NULL references tab_a ON DELETE CASCADE,
  bar text
);

begin;
insert into tab_a (foo) values ('test') returning *;
insert into tab_b (a_id, bar) values (1, 'test') returning *;
commit;

Upvotes: 0

Mehmet AVŞAR
Mehmet AVŞAR

Reputation: 521

The statement below allows non-deferrable constraints to be deferred until transaction committed. If you don't want to change FK definitions.

SET CONSTRAINTS ALL DEFERRED;

Upvotes: 7

Frank Heikens
Frank Heikens

Reputation: 126953

You can make a deferrable FK, just use DEFERRABLE and maybe INITIALLY DEFERRED, that's up to you.

http://www.postgresql.org/docs/current/static/sql-createtable.html

Upvotes: 22

Related Questions