CODEWITHSUNDEEP
javasqldatabasepostgresql
Nosrettap
Nosrettap

Reputation: 11320

How to create an assertion in JDBC?

I'm using Postgresql and JDBC and I'm trying to figure out how to create an assertion.

I currently have two relations table A(url varchar(30)) and table B(url varchar(30)) and I would like to write an assertion so that it is impossible to insert into table B if that element already exists in table A. How do I do this in JDBC? If it's not possible to use assertions, how would I make something similar happen?

EDIT: Ok, so I've read that it's not possible to create an assertion using postgresql; thus, my question is now, how would I do something similar using the constraints of Postgresql and JDBC?

Upvotes: 0

Views: 205

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324521

What you want is effectively a negative foreign key constraint, which isn't directly supported. You can implement this constraint using a PL/PgSQL BEFORE INSERT OR UPDATE trigger function that tests the condition.

See PL/PgSQL trigger procedures in the Pg docs.

You'll probably need to use SERIALIZABLE isolation mode to handle races where one transaction inserts into B while the other inserts into A. You'll need the improved serializable isolation support in 9.1/9.2.

Upvotes: 1

Jan Kotek
Jan Kotek

Reputation: 1084

I am not sure there is not typo, perhaps 'JDBM' should be 'JDBC'?

For MapDB (aka JDBM4) there is plan to implement map modification listeners, which would handle similar situations. There is no functionality like this in JDBM3.

For now I would recommend not to use assertions and check it in your code.

Upvotes: 1

Related Questions