From: Richard Huxton <dev@archonet.com>
To: Gianluca Riccardi <ml-reader@moonwatcher.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: DB design and foreign keys
Date: Tue, 13 Dec 2005 18:59:47 +0000
Message-ID: <439F1A23.40202@archonet.com> (raw)
In-Reply-To: <439F0FE8.1050503@moonwatcher.it>
References: <439F0FE8.1050503@moonwatcher.it>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, ml-reader@moonwatcher.it
Subject: Re: DB design and foreign keys
In-Reply-To: <439F1A23.40202@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox