Thread: auto increment within a compound key

Hi,

I want to create a compound primary key. The elements of this primary key should be
the fields called nb (int) and d (double). nb should be default and autoincremented,
so that the following four inserts

insert into mytable (ts) values ( 1.0 )
insert into mytable (ts) values ( 1.1 )
insert into mytable (ts) values ( 1.1 )
insert into mytable (ts) values ( 2.0 )

delieveres me the following table content:

nb               ts
----------------------------------
1             1.0
1                1.1
2             1.1
1                2.0

In MySQL the create table statement would look like:

CREATE TABLE mytable  (
        nb INT NOT NULL AUTO_INCREMENT,
        timeStamp DOUBLE NOT NULL,
        PRIMARY KEY (timeStamp, id)
)

Postgres gives me (using serial as replacement for AUTO_INCREMENT):

nb               ts
----------------------------------
1             1.0
2                1.1
3             1.1
4                2.0

Is there any way to achieve the same result in Postgresql as the MySQL AUTO_INCREMENT does?

Thanks.

Yours
   Dieter

On Tue, Jan 17, 2006 at 04:33:46PM +0100, Bogdoll, Dieter wrote:
> Is there any way to achieve the same result in Postgresql as the MySQL
> AUTO_INCREMENT does?

Not without an adverse impact on performance.  PostgreSQL's serial
type is just a notational convenience for an integer column that
takes its default value from a sequence; the sequence's value isn't
affected by other columns' values.  You could achieve the effect
you're after with a trigger but you'd have to allow for concurrency.

I see that a table definition such as the one you posted isn't
allowed for InnoDB tables:

mysql> CREATE TABLE foo (
    -> x int AUTO_INCREMENT,
    -> y int,
    -> PRIMARY KEY (y, x)
    -> ) TYPE INNODB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

It's allowed if you reverse the order of the columns in the primary
key, but then you get the same effect as in PostgreSQL:

mysql> CREATE TABLE foo (
    -> x int AUTO_INCREMENT,
    -> y int,
    -> PRIMARY KEY (x, y)
    -> ) TYPE INNODB;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> INSERT INTO foo (y) VALUES (1), (1), (2);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM foo;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+---+---+
3 rows in set (0.01 sec)

--
Michael Fuhr