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