Обсуждение: 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