Re: DB design and foreign keys

Gianluca Riccardi wrote:
> hello all,
> i'm usign PostgreSQL 7.4.7 in a Debian 3.1
> 

> CREATE TABLE orders  (
>   id serial,
>   order_code serial,
...
>   PRIMARY KEY (id, order_code)
> );
> 
> CREATE TABLE order_items (
>   id serial,
>   order_code integer REFERENCES orders (order_code) NOT NULL,

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

> ERROR:  there is no unique constraint matching given keys for referenced 
> table "orders"

It means what it says. You have defined table orders with a primary key 
of (id,order_code). This means that the combination of (id,order_code) 
must be unique. So - these could all exist at the same time: (1,1), (1,2), (2,1), (2,2)
You could not then add another (1,2) combination.

Since id and order_code are both just automatically-generated numbers in 
the orders table it doesn't add anything to make both of them part of a 
primary-key. I would delete the id column altogether and just have the 
order_code as the primary-key (since "order_code" carries more meaning 
to a human than "id"). This means your order_items table can then safely 
reference the order_code it wants to.

HTH
--   Richard Huxton  Archonet Ltd