From shavonne.marietta@studioform.it Fri Feb  8 11:16:11 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 50D5F2E106F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 07:16:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 11680-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 07:15:56 -0400 (AST)
Received: from smtpout26.attiva.biz (smtpout26.attiva.biz [85.37.16.27])
	by postgresql.org (Postfix) with ESMTP id EB12A2E30F0
	for <pgsql-sql@postgresql.org>; Fri,  8 Feb 2008 07:16:01 -0400 (AST)
Received: from fbcmfe02b10.fbc.local ([192.168.30.91]) by smtpout26.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Fri, 8 Feb 2008 12:15:57 +0100
Received: from dream ([88.34.161.130]) by fbcmfe02b10.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Fri, 8 Feb 2008 12:15:48 +0100
Message-ID: <00a401c86a43$f88de120$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: <pgsql-sql@postgresql.org>
Subject: Serial not nulla
Date: Fri, 8 Feb 2008 12:15:53 +0100
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00A1_01C86A4C.59F7CD10"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 08 Feb 2008 11:15:48.0616 (UTC)
	FILETIME=[F5050880:01C86A43]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/59
X-Sequence-Number: 30150

This is a multi-part message in MIME format.

------=_NextPart_000_00A1_01C86A4C.59F7CD10
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello

I am working with a database that has a Index number defined as Serial =
NOT NULL

I used this because,
1. I want to make sure that when many users login at the same time the =
Index number won't be repeated.
2. I don't have to increment it by programming (I use ASP)

But now i have a situation that i need to index numbers. For Example i =
could have a structure like this

INDEX1 - N_SHEET - TOT_SHEET
1 - 1 - 1
2 - 1 - 3
2 - 2 - 3
2 - 3 - 3

N_SHEET and TOT_SHEET are filled by the user (via web) but i need to =
hold on to the INDEX. And while userA is filling the 3 row if userB =
loggs in i need to provide the INDEX1 with 3.

Any idea??

Thanks

Shavonne

Shavonne Wijesinghe
www.studioform.it
=20
Le informazioni contenute nella presente comunicazione e i relativi =
allegati possono essere riservate e sono, comunque destinate =
esclusivamente alle persone o alla Societ=E0 sopra indicati.
La diffusione, distribuzione e/o copiature del documento trasmesso da =
parte di qualsiasi soggetto diverso dal destinatario =E8 proibita, sia =
ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003.
=20
Se avete ricevuto questo messaggio per errore, Vi preghiamo di =
distruggerlo e di informarci immediatamente per telefono allo =
0039362595044 o inviando un messaggio all'indirizzo e-mail
amministrazione@studioform.it
=20
The informations in this communication is confidential and may also be =
legally privileged. It is intended for the addressee only.
Access to this e-mail by anyone else is unauthorized. It is not to be =
relied upon by any person other than the addressee, except with our =
prior written approval. If you received this message please send an =
e-mail to the sender.
------=_NextPart_000_00A1_01C86A4C.59F7CD10
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16587" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello<BR><BR>I am working with a =
database that has=20
a Index number defined as Serial NOT NULL<BR><BR>I used this =
because,<BR>1. I=20
want to make sure that when many users login at the same time the Index =
number=20
won't be repeated.<BR>2. I don't have to increment it by programming (I =
use=20
ASP)<BR><BR>But now i have a situation that i need to index numbers. For =
Example=20
i could have a structure like this<BR><BR>INDEX1 - N_SHEET - =
TOT_SHEET<BR>1 - 1=20
- 1<BR>2 - 1 - 3<BR>2 - 2 - 3<BR>2 - 3 - 3<BR><BR>N_SHEET and TOT_SHEET =
are=20
filled by the user (via web) but i need to hold on to the INDEX. And =
while userA=20
is filling the 3 row if userB loggs in i need to provide the INDEX1 with =

3.<BR><BR>Any idea??<BR><BR>Thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Shavonne</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Shavonne Wijesinghe<BR><A=20
href=3D"http://www.studioform.it">www.studioform.it</A><BR>&nbsp;<BR>Le=20
informazioni contenute nella presente comunicazione e i relativi =
allegati=20
possono essere riservate e sono, comunque destinate esclusivamente alle =
persone=20
o alla Societ=E0 sopra indicati.<BR>La diffusione, distribuzione e/o =
copiature del=20
documento trasmesso da parte di qualsiasi soggetto diverso dal =
destinatario =E8=20
proibita, sia ai sensi dell=92art. 616 c.p., che ai sensi del D. Lgs. n. =

196/2003.<BR>&nbsp;<BR>Se avete ricevuto questo messaggio per errore, Vi =

preghiamo di distruggerlo e di informarci immediatamente per telefono =
allo=20
0039362595044 o inviando un messaggio all=92indirizzo e-mail<BR><A=20
href=3D"mailto:amministrazione@studioform.it">amministrazione@studioform.=
it</A><BR>&nbsp;<BR>The=20
informations in this communication is confidential and may also be =
legally=20
privileged. It is intended for the addressee only.<BR>Access to this =
e-mail by=20
anyone else is unauthorized. It is not to be relied upon by any person =
other=20
than the addressee, except with our prior written approval. If you =
received this=20
message please send an e-mail to the sender.</FONT></DIV></BODY></HTML>

------=_NextPart_000_00A1_01C86A4C.59F7CD10--




From pgsql@Sheeky.Biz Fri Feb  8 13:18:01 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 9111F2E00F3
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 09:18:01 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 46238-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 09:17:58 -0400 (AST)
Received: from ipmail01.adl6.internode.on.net (ipmail01.adl6.internode.on.net
	[203.16.214.146])
	by postgresql.org (Postfix) with ESMTP id 9D82E2E2DAF
	for <pgsql-sql@postgresql.org>; Fri,  8 Feb 2008 09:17:57 -0400 (AST)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: Ah4FAFblq0fLevdH/2dsb2JhbAAsgS2qSg
X-IronPort-AV: E=Sophos;i="4.25,321,1199626200"; d="scan'208";a="37846290"
Received: from ppp247-71.static.internode.on.net (HELO DevBox.local)
	([203.122.247.71])
	by ipmail01.adl6.internode.on.net with ESMTP; 08 Feb 2008 23:47:53 +1030
Message-ID: <47AC567F.9020907@Sheeky.Biz>
Date: Fri, 08 Feb 2008 23:47:51 +1030
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
CC: pgsql-sql@postgresql.org
Subject: Re: Serial not nulla
References: <00a401c86a43$f88de120$3102a8c0@dream>
In-Reply-To: <00a401c86a43$f88de120$3102a8c0@dream>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/61
X-Sequence-Number: 30152

Shavonne Marietta Wijesinghe wrote:
> Hello
> 
> I am working with a database that has a Index number defined as Serial NOT NULL
> 
> I used this because,
> 1. I want to make sure that when many users login at the same time the Index number won't be repeated.
> 2. I don't have to increment it by programming (I use ASP)
> 
> But now i have a situation that i need to index numbers. For Example i could have a structure like this
> 
> INDEX1 - N_SHEET - TOT_SHEET
> 1 - 1 - 1
> 2 - 1 - 3
> 2 - 2 - 3
> 2 - 3 - 3
> 
> N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold on to the INDEX. And while userA is filling the 3 row if userB loggs in i need to provide the INDEX1 with 3.
> 
> Any idea??
> 

As well as using the "Serial NOT NULL" you have also defined this column 
as PRIMARY KEY (or a unique index) which is what is preventing the 
duplicates in that column. (A primary key is enforced with a unique index)


 From the sample shown you can use all three columns as the primary key 
with something similar to -

ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET", 
"TOT_SHEET");

(this implies that for each user they will have only one row for each 
combination of N_SHEET and TOT_SHEET) If you need to allow them to 
select the same 2 sheet numbers more than once then I would suggest you 
have an extra column for a primary key and redefine INDEX1 as the 
user_id. (or just add a user_id column and leave the INDEX1 as it is)



It's not recommended but you could also have the table without a primary 
key allowing duplicate value combinations. This would prevent you 
updating a single row though.



-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz



From shavonne.marietta@studioform.it Mon Feb  11 10:38:11 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 32DA12E0337
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 06:38:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 79070-01-6 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 06:38:03 -0400 (AST)
Received: from smtpout13.attiva.biz (smtpout13.attiva.biz [85.37.16.15])
	by postgresql.org (Postfix) with ESMTP id 6B5052E2D6E
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 06:25:02 -0400 (AST)
Received: from FBCMFE02B12.fbc.local ([192.168.30.89]) by smtpout13.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Mon, 11 Feb 2008 11:24:57 +0100
Received: from dream ([88.34.161.130]) by FBCMFE02B12.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Mon, 11 Feb 2008 11:24:51 +0100
Message-ID: <009b01c86c98$5ba507b0$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: "Shane Ambler" <pgsql@Sheeky.Biz>
Cc: <pgsql-sql@postgresql.org>
References: <00a401c86a43$f88de120$3102a8c0@dream>
	<47AC567F.9020907@Sheeky.Biz>
Subject: Re: Serial not nulla
Date: Mon, 11 Feb 2008 11:24:59 +0100
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 11 Feb 2008 10:24:51.0336 (UTC)
	FILETIME=[55FA6080:01C86C98]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/73
X-Sequence-Number: 30164

Thanks alot Shane..Just what I wanted.. Didn't think of a solution with 3 
primary keys.

Have a nice day ^___^


Shavonne Wijesinghe
www.studioform.it


----- Original Message ----- 
From: "Shane Ambler" <pgsql@Sheeky.Biz>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, February 08, 2008 2:17 PM
Subject: Re: [SQL] Serial not nulla


> Shavonne Marietta Wijesinghe wrote:
>> Hello
>>
>> I am working with a database that has a Index number defined as Serial 
>> NOT NULL
>>
>> I used this because,
>> 1. I want to make sure that when many users login at the same time the 
>> Index number won't be repeated.
>> 2. I don't have to increment it by programming (I use ASP)
>>
>> But now i have a situation that i need to index numbers. For Example i 
>> could have a structure like this
>>
>> INDEX1 - N_SHEET - TOT_SHEET
>> 1 - 1 - 1
>> 2 - 1 - 3
>> 2 - 2 - 3
>> 2 - 3 - 3
>>
>> N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold 
>> on to the INDEX. And while userA is filling the 3 row if userB loggs in i 
>> need to provide the INDEX1 with 3.
>>
>> Any idea??
>>
>
> As well as using the "Serial NOT NULL" you have also defined this column 
> as PRIMARY KEY (or a unique index) which is what is preventing the 
> duplicates in that column. (A primary key is enforced with a unique index)
>
>
> From the sample shown you can use all three columns as the primary key 
> with something similar to -
>
> ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
> ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET", 
> "TOT_SHEET");
>
> (this implies that for each user they will have only one row for each 
> combination of N_SHEET and TOT_SHEET) If you need to allow them to select 
> the same 2 sheet numbers more than once then I would suggest you have an 
> extra column for a primary key and redefine INDEX1 as the user_id. (or 
> just add a user_id column and leave the INDEX1 as it is)
>
>
>
> It's not recommended but you could also have the table without a primary 
> key allowing duplicate value combinations. This would prevent you updating 
> a single row though.
>
>
>
> -- 
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz