Postgres Pro
Downloads
Home   >   mailing lists

Thread: PRIMARY KEY

PRIMARY KEY

From
"Shavonne Marietta Wijesinghe"
Date:
Hello
 
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
 
And i insert the rows via a form in ASP. When the form loads i have a functin that goes and gets the value of the field N_GEN adds 1 to it and shows it to the user.
The problem is when i have 2 users working at the same time.
 
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
 
So they fill in the form and user A clicks on the button OK and the record has been inserted with N_GEN = 3. But when the user B clicks on the button the record is not inserted because it has the same key "3"
 
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
 
Is there any way i can do this automatically? i mean maybe i have to use someother property instead of "Primary Key" ??
 
Thanks
 
Shavonne Wijesinghe

Re: PRIMARY KEY

From
Richard Huxton
Date:
Shavonne Marietta Wijesinghe wrote:
> Hello
> 
> I have created a table
> CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
> 
> And i insert the rows via a form in ASP. When the form loads i have a functin that goes and gets the value of the
fieldN_GEN adds 1 to it and shows it to the user.
 
> The problem is when i have 2 users working at the same time.

Check the manuals for "sequences" and "serial type".

--   Richard Huxton  Archonet Ltd

Re: PRIMARY KEY

From
"M.P.Dankoor"
Date:
Hello,

Is it possible to redesign your table as follows:

create table Mod48_00_2007 (
ID            text,
N_GEN         serial     not null,
FORMSTORE     text,
COD_NOTATIO   text,
PA_COGNOME    text,
constraint pk_Mod48_00_2007 primary key (N_GEN)
);

Your insert simply becomes:
INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')

Do note that you do not refer to the N_GEN column, it will use the next value, please refer to http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
for more information.

Mario

Shavonne Marietta Wijesinghe wrote:
Hello
 
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
 
And i insert the rows via a form in ASP. When the form loads i have a functin that goes and gets the value of the field N_GEN adds 1 to it and shows it to the user.
The problem is when i have 2 users working at the same time.
 
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
 
So they fill in the form and user A clicks on the button OK and the record has been inserted with N_GEN = 3. But when the user B clicks on the button the record is not inserted because it has the same key "3"
 
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
 
Is there any way i can do this automatically? i mean maybe i have to use someother property instead of "Primary Key" ??
 
Thanks
 
Shavonne Wijesinghe

Re: PRIMARY KEY

From
"M.P.Dankoor"
Date:
Phillip Smith wrote: <blockquote cite="mid1173269986.4925.64.camel@phillip-desktop" type="cite"> If you actually need
toknow the value of N_GEN in your ASP application, you will need to query the database first and select the NEXTVAL
fromthe sequence that the "serial" data type will create, then use that returned value in your insert - ie, DON'T
excludeit from the insert, otherwise it will default to NEXTVAL again and return a different value.<br /><br /> The
onlycatch with this is that you most likely won't end up with contiguous values in this column - ie, if a user cancels
afteryou seect nextval, but before you insert - the value of the sequence has already increased, and will be increased
againbefore returning a value when you next select nextval<br /><br /></blockquote><br /> That's precisely why I would
notuse the nextval call, you might end up with gaps. Moreover you are making 2 database calls instead of one.<br /> But
thenagain, if your application needs to know the value you can't really avoid using nextval (good thing it's
concurrencyproof)<br /><br /> Mario<br /><br /><br /> Phillip Smith wrote: <blockquote
cite="mid1173269986.4925.64.camel@phillip-desktop"type="cite"> If you actually need to know the value of N_GEN in your
ASPapplication, you will need to query the database first and select the NEXTVAL from the sequence that the "serial"
datatype will create, then use that returned value in your insert - ie, DON'T exclude it from the insert, otherwise it
willdefault to NEXTVAL again and return a different value.<br /><br /> The only catch with this is that you most likely
won'tend up with contiguous values in this column - ie, if a user cancels after you seect nextval, but before you
insert- the value of the sequence has already increased, and will be increased again before returning a value when you
nextselect nextval<br /><br /> Cheers,<br /> ~p<br /><br /> On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote:<br
/><blockquotetype="CITE"> Hello,<br /><br /> Is it possible to redesign your table as follows:<br /><br /> create table
Mod48_00_2007(<br /> ID            text,<br /> N_GEN         serial     not null,<br /> FORMSTORE     text,<br />
COD_NOTATIO  text,<br /> PA_COGNOME    text,<br /> constraint pk_Mod48_00_2007 primary key (N_GEN)<br /> );<br /><br />
Yourinsert simply becomes: </blockquote><blockquote type="CITE"> INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO,
PA_COGNOME)VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS') </blockquote><blockquote type="CITE"><br /> Do
notethat you do not refer to the N_GEN column, it will use the next value, please refer to <a
href="http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL">http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL</a><br
/>for more information.<br /><br /> Mario<br /><br /> Shavonne Marietta Wijesinghe wrote: </blockquote><blockquote
type="CITE"><blockquotetype="CITE"><font size="2">Hello</font></blockquote></blockquote><blockquote
type="CITE"><blockquotetype="CITE">   </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE"> I
have createda table </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE"> CREATE TABLE
MOD48_00_2007( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
</blockquote></blockquote><blockquotetype="CITE"><blockquote type="CITE">   </blockquote></blockquote><blockquote
type="CITE"><blockquotetype="CITE"> And i insert the rows via a form in ASP. When the form loads i have a functin that
goesand gets the value of the field N_GEN adds 1 to it and shows it to the user. </blockquote></blockquote><blockquote
type="CITE"><blockquotetype="CITE"> The problem is when i have 2 users working at the same time.
</blockquote></blockquote><blockquotetype="CITE"><blockquote type="CITE">   </blockquote></blockquote><blockquote
type="CITE"><blockquotetype="CITE"> For example the last value in my field N_GEN is 2
</blockquote></blockquote><blockquotetype="CITE"><blockquote type="CITE"> When both the users A and B loads the form
(ASPpage) it sees N_GEN = 3 :) </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE">  
</blockquote></blockquote><blockquotetype="CITE"><blockquote type="CITE"> So they fill in the form and user A clicks on
thebutton OK and the record has been inserted with N_GEN = 3. But when the user B clicks on the button the record is
notinserted because it has the same key "3" </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE">
 </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE"> INSERT INTO MOD48_00_2007 (ID, N_GEN,
FORMSTORE,COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
</blockquote></blockquote><blockquotetype="CITE"><blockquote type="CITE">   </blockquote></blockquote><blockquote
type="CITE"><blockquotetype="CITE"> Is there any way i can do this automatically? i mean maybe i have to use someother
propertyinstead of "Primary Key" ?? </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE">  
</blockquote></blockquote><blockquotetype="CITE"><blockquote type="CITE"> Thanks </blockquote></blockquote><blockquote
type="CITE"><blockquotetype="CITE">   </blockquote></blockquote><blockquote type="CITE"><blockquote type="CITE"><font
size="2">ShavonneWijesinghe</font><br /><br /></blockquote></blockquote><blockquote type="CITE"><br /></blockquote><br
/><p><b>*******************Confidentialityand Privilege Notice*******************</b><p>The material contained in this
messageis privileged and confidential to the addressee. If you are not the addressee indicated in this message or
responsiblefor delivery of the message to such person, you may not copy or deliver this message to anyone, and you
shoulddestroy it and kindly notify the sender by reply email. <p>Information in this message that does not relate to
theofficial business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its
employees,contractors or associates shall not be liable for direct, indirect or consequential loss arising from
transmissionof this message or any attachments <br /></blockquote><br />

Re: PRIMARY KEY

From
"Shavonne Marietta Wijesinghe"
Date:
Thanks alot Mario. It did exactly what i wanted. I shall also take alook at the link you gave for more details..
 
 
Shavonne Wijesinghe
 
----- Original Message -----
Sent: Wednesday, March 07, 2007 12:57 PM
Subject: Re: [SQL] PRIMARY KEY

Hello,

Is it possible to redesign your table as follows:

create table Mod48_00_2007 (
ID            text,
N_GEN         serial     not null,
FORMSTORE     text,
COD_NOTATIO   text,
PA_COGNOME    text,
constraint pk_Mod48_00_2007 primary key (N_GEN)
);

Your insert simply becomes:
INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')

Do note that you do not refer to the N_GEN column, it will use the next value, please refer to http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
for more information.

Mario

Shavonne Marietta Wijesinghe wrote:
Hello
 
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
 
And i insert the rows via a form in ASP. When the form loads i have a functin that goes and gets the value of the field N_GEN adds 1 to it and shows it to the user.
The problem is when i have 2 users working at the same time.
 
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
 
So they fill in the form and user A clicks on the button OK and the record has been inserted with N_GEN = 3. But when the user B clicks on the button the record is not inserted because it has the same key "3"
 
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
 
Is there any way i can do this automatically? i mean maybe i have to use someother property instead of "Primary Key" ??
 
Thanks
 
Shavonne Wijesinghe