Message-ID: <439F1A23.40202@archonet.com>
Date: Tue, 13 Dec 2005 18:59:47 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051025)
MIME-Version: 1.0
To: Gianluca Riccardi <ml-reader@moonwatcher.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: DB design and foreign keys
References: <439F0FE8.1050503@moonwatcher.it>
In-Reply-To: <439F0FE8.1050503@moonwatcher.it>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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