Re: making a rule and know when it is violated

What is the message returned by the sqlexception if such an index is
violated? I searched it on google.com, but didn't find much interesting,
probably using the wrong keywords...
Nico.

"Kris Jurka" <books(at)ejurka(dot)com> schreef in bericht
news:Pine(dot)BSO(dot)4(dot)56(dot)0502211107470(dot)29249(at)leary(dot)csoft(dot)net(dot)(dot)(dot)
>
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>