Re: DB design and foreign keys

Gianluca Riccardi <ml-reader@moonwatcher.it> writes:
> 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)
> );

> when i try to create the table order_items postgresql gives the 
> following error:

> business-test-db=# CREATE TABLE order_items (
> business-test-db(#    id serial,
> business-test-db(#    order_code integer REFERENCES orders (order_code) 
> NOT NULL,
> business-test-db(#    customer_code integer REFERENCES customers 
> (customer_code) NOT NULL,
> business-test-db(#    product_code varchar(60) REFERENCES products 
> (code) NOT NULL,
> business-test-db(#    qty int NOT NULL,
> business-test-db(#    price numeric REFERENCES products (price) NOT NULL,
> business-test-db(#    row_price numeric,
> business-test-db(#    PRIMARY KEY (id, order_code)
> business-test-db(# );
> NOTICE:  CREATE TABLE will create implicit sequence "order_items_id_seq" 
> for "serial" column "order_items.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
> "order_items_pkey" for table "order_items"
> ERROR:  there is no unique constraint matching given keys for referenced 
> table "orders"

> i'm a RTFM man, but i miss the point from the documentation obviously, 
> because what i don't understand is why the referenced column isn't 
> considered to be unique.

order_code is not by itself unique --- SERIAL doesn't guarantee that.
I'm not sure why you are declaring the primary key of orders as being
the combination of *two* serial columns, but if that's what you really
need and you also want to be able to reference a row by just one of
them, you'll need to apply a separate unique constraint to just the
order_code column.
        regards, tom lane