Subject: Re: DB design and foreign keys
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Gianluca Riccardi <ml-reader@moonwatcher.it>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <439F0FE8.1050503@moonwatcher.it>
References: <439F0FE8.1050503@moonwatcher.it>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1134500394.3587.115.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 13 Dec 2005 12:59:54 -0600

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.