pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSerial not nulla
3+ messages / 2 participants
[nested] [flat]
* Serial not nulla
@ 2008-02-08 11:15 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 3+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-08 11:15 UTC (permalink / raw)
To: pgsql-sql
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
Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati.
La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003.
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
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Serial not nulla
@ 2008-02-08 13:17 Shane Ambler <pgsql@Sheeky.Biz>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 3+ messages in thread
From: Shane Ambler @ 2008-02-08 13:17 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Serial not nulla
@ 2008-02-11 10:24 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 0 replies; 3+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-11 10:24 UTC (permalink / raw)
To: Shane Ambler <pgsql@Sheeky.Biz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-02-11 10:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-08 11:15 Serial not nulla Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-08 13:17 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-02-11 10:24 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox