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.