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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
