Re: making a rule and know when it is violated

On Mon, 21 Feb 2005, Nico wrote:

> Hello, I want to make a rule in phpPgAdmin(PostgreSQL 7.3.8-RH ). Then I
> want to intercept any violation on that rule in java. Here is the table
> creation code: CREATE TABLE "tblUser" (
>     "UserID" serial NOT NULL,
>     "UserName" character varying(50) NOT NULL,
>     "UserPass" character varying(50) NOT NULL,
>     "UserRoleID" integer DEFAULT 3 NOT NULL
> );
> Now it's imperative that the field UserRoleID has maximum one record that
> contains the integer 1. Possible other values are 2 and 3. They are allowed
> to exist in more than one record.

Enforcing this constraint in the database can be done with a partial
unique index:

CREATE UNIQUE INDEX myi ON mytable(mycol) WHERE mycol = 1;

> After that I need to know in java how to know when a violation of this rule
> occurs.

The server will report a violation of this with an
SQLException.getSQLState value indicating a duplicate key exception.  This
is no different than any other duplicate key exception so there is no real
way to tie it back to what particular constrain is violated without trying
to look in the actual message text.  Actually re-reading the above you
mention 7.3.X which does not support SQLState values (this is a 7.4+
feature), so you're only feedback will be the message text itself.

Kris Jurka