Re: DB design and foreign keys

On Tue, 2005-12-13 at 12:16, Gianluca Riccardi wrote:
> hello all,
> i'm usign PostgreSQL 7.4.7 in a Debian 3.1
>
> following is the SQL schema of my (very)small DB for a (very small)web
> business application:

> CREATE TABLE orders (
> id serial,
> order_code serial,
> customer_code integer REFERENCES customers (customer_code) NOT NULL,
> order_date time without time zone NOT NULL,
> remote_ip inet NOT NULL,
> order_time timestamp with time zone NOT NULL,
> order_type varchar(10) NOT NULL,
> state varchar(10) NOT NULL,
> PRIMARY KEY (id, order_code)
> );

Given this table layout, I'm gonna take a wild guess and ask if you're
coming from MySQL and expecting the second serial order_code to be a
sub-autoincrement to id? If so, it won't be. That's a mysqlism. If
you want something similar, you'll have to implement it yourself, and
note that such a thing tends to be a poor performer with lots of
parallel updates, and it can also be susceptible to race conditions if
no locking is used.