Thread: Transaction foreign key violation

I'm getting a foreign key constraint violation if I run a transaction in
which the first statement inserts a new row in Table1 (with serial as
pk, "idTable1") and the second statement gets this "idTable1" as a fk.
Because it is not there yet (but in the same transaction) I get this
error. If I don't use transaction it is working.
What can I do?
This fk constraint is set DEFERRABLE INITIALLY DEFERRED. I tried "SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE" (just with query.execute()
before the other statements)) - no luck. If I commit the statements via
pgAdmin with BEGIN; INSERT INTO ..; COMMIT; it is working fine.
I use 8.1-405 JDBC3

Thanks for suggestions!
Martina

Martina wrote:
>
> I'm getting a foreign key constraint violation if I run a transaction in
> which the first statement inserts a new row in Table1 (with serial as
> pk, "idTable1") and the second statement gets this "idTable1" as a fk.
> Because it is not there yet (but in the same transaction) I get this
> error. If I don't use transaction it is working.
> What can I do?

Can you show us your code, or (better) a small testcase that shows the
problem?

> I tried "SET
> TRANSACTION ISOLATION LEVEL SERIALIZABLE" (just with query.execute()
> before the other statements)) - no luck.

The right way to do this is via Connection.setTransactionIsolation().

-O