Re: DB design and foreign keys

Gianluca Riccardi <ml-reader(at)moonwatcher(dot)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