Обсуждение: making a rule and know when it is violated
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.
After that I need to know in java how to know when a violation of this rule
occurs.
Anyone any ideas?
Kind regards,
Nico.
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
"Nico" <nicohmail-postgresql 'at' yahoo.com> writes: > 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. > After that I need to know in java how to know when a violation of this rule > occurs. Why not using a trigger function in postgres? -- Guillaume Cottenceau
I don't know how to write it in postgresql. "Guillaume Cottenceau" <gc@mnc.ch> schreef in bericht news:87acpxlwkx.fsf@meuh.mnc.ch... > "Nico" <nicohmail-postgresql 'at' yahoo.com> writes: > >> 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. >> After that I need to know in java how to know when a violation of this >> rule >> occurs. > > Why not using a trigger function in postgres? > > -- > Guillaume Cottenceau > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Thanks it was very usefull. Nico. "Kris Jurka" <books@ejurka.com> schreef in bericht news:Pine.BSO.4.56.0502211107470.29249@leary.csoft.net... > > > 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@postgresql.org) >
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@ejurka.com> schreef in bericht news:Pine.BSO.4.56.0502211107470.29249@leary.csoft.net... > > > 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@postgresql.org) >
On Tue, 22 Feb 2005, Nico wrote: > 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... You could of course take the ten seconds necessary to try it: jurka=# create table ut (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ut_pkey" for table "ut" CREATE TABLE jurka=# insert into ut values (1); INSERT 608266 1 jurka=# insert into ut values (1); ERROR: duplicate key violates unique constraint "ut_pkey" Kris Jurka