PostgreSQL : Transaction and foreign key problem

问题:

I'm inserting a value in table A, that has a serial type as primary key. I wanna use the returned value of the query as a foreign key of table B... but I get this message:

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 make this possible without: - starting a new Transaction - droping my foreign keys constraints =O ?

Regards! Pedro

回答1:

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

回答2:

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;