From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gianluca Riccardi <ml-reader@moonwatcher.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: DB design and foreign keys
Date: Tue, 13 Dec 2005 13:31:29 -0500
Message-ID: <8787.1134498689@sss.pgh.pa.us> (raw)
In-Reply-To: <439F0FE8.1050503@moonwatcher.it>
References: <439F0FE8.1050503@moonwatcher.it>
Gianluca Riccardi <ml-reader@moonwatcher.it> writes:
> CREATE TABLE orders (
> id serial,
> order_code serial,
> customer_code integer REFERENCES customers (customer_code) NOT NULL,
> order_date time without time zone NOT NULL,
> remote_ip inet NOT NULL,
> order_time timestamp with time zone NOT NULL,
> order_type varchar(10) NOT NULL,
> state varchar(10) NOT NULL,
> PRIMARY KEY (id, order_code)
> );
> when i try to create the table order_items postgresql gives the
> following error:
> business-test-db=# CREATE TABLE order_items (
> business-test-db(# id serial,
> business-test-db(# order_code integer REFERENCES orders (order_code)
> NOT NULL,
> business-test-db(# customer_code integer REFERENCES customers
> (customer_code) NOT NULL,
> business-test-db(# product_code varchar(60) REFERENCES products
> (code) NOT NULL,
> business-test-db(# qty int NOT NULL,
> business-test-db(# price numeric REFERENCES products (price) NOT NULL,
> business-test-db(# row_price numeric,
> business-test-db(# PRIMARY KEY (id, order_code)
> business-test-db(# );
> NOTICE: CREATE TABLE will create implicit sequence "order_items_id_seq"
> for "serial" column "order_items.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "order_items_pkey" for table "order_items"
> ERROR: there is no unique constraint matching given keys for referenced
> table "orders"
> i'm a RTFM man, but i miss the point from the documentation obviously,
> because what i don't understand is why the referenced column isn't
> considered to be unique.
order_code is not by itself unique --- SERIAL doesn't guarantee that.
I'm not sure why you are declaring the primary key of orders as being
the combination of *two* serial columns, but if that's what you really
need and you also want to be able to reference a row by just one of
them, you'll need to apply a separate unique constraint to just the
order_code column.
regards, tom lane
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: tgl@sss.pgh.pa.us, ml-reader@moonwatcher.it
Subject: Re: DB design and foreign keys
In-Reply-To: <8787.1134498689@sss.pgh.pa.us>
* 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