Thread: Composed Key and autoincrement

Hello,
I want to create a table with a primary key composed of 2 auto increment
(serial) value.
I don't speak english very well so I'gonna write you an example to let
you understand what's
my problem. Hope someone can help me.

For example

CREATE TABLE foo(
    keypartone   serial not null,
    keyparttwo   serial not null,
    var1              integer not null,
    PRIMARY KEY( keypartone   , keyparttwo   )
    );

If I do this inserts

INSERT INTO foo ( var1 ) VALUES (  7 );
INSERT INTO foo ( var1 ) VALUES (  9 );
INSERT INTO foo ( keypartone, var1 ) VALUES (  1, 111 );
INSERT INTO foo ( keypartone, var1 ) VALUES (  2, 3  );

I get this table

| 1 | 1 | 7 |
| 2 | 2 | 9 |
| 1 | 3 | 111 |
| 2 | 4 | 3 |


There is a way to program the 2 sequences to have the following result
instead???

INSERT INTO foo ( var1 ) VALUES (  7 );
INSERT INTO foo ( var1 ) VALUES (  9 );
INSERT INTO foo ( keypartone, var1 ) VALUES (  1, 111 );
INSERT INTO foo ( keypartone, var1 ) VALUES (  2, 3  );

| 1 | 1 | 7 |
| 2 | 1 | 9 |
| 1 | 2 | 111 |
| 2 | 2 | 3 |

There is a  way to do this?

Thank you a lot
Taioli Fabiano

fbn <fbn79@libero.it> writes:
> I want to create a table with a primary key composed of 2 auto increment
> (serial) value.

I do not understand why you think that's a good idea.  If you're worried
that 2^32 values isn't enough, use a bigserial.

            regards, tom lane

you could put your business logic into a trigger which is called on
insert, postgres (and me) don't know why or when you want to change the
second column.
Or, you could make a constraint on the column (maybe?)





____________________________________________________________________________________
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
http://mobile.yahoo.com/mail