X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7B5219DC897 for ; Tue, 13 Dec 2005 14:31:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69033-07 for ; Tue, 13 Dec 2005 14:31:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 255E69DC896 for ; Tue, 13 Dec 2005 14:31:36 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBDIVThm008788; Tue, 13 Dec 2005 13:31:29 -0500 (EST) To: Gianluca Riccardi cc: pgsql-sql@postgresql.org Subject: Re: DB design and foreign keys In-reply-to: <439F0FE8.1050503@moonwatcher.it> References: <439F0FE8.1050503@moonwatcher.it> Comments: In-reply-to Gianluca Riccardi message dated "Tue, 13 Dec 2005 19:16:08 +0100" Date: Tue, 13 Dec 2005 13:31:29 -0500 Message-ID: <8787.1134498689@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.003 required=5 tests=[AWL=0.003] X-Spam-Score: 0.003 X-Spam-Level: X-Archive-Number: 200512/100 X-Sequence-Number: 23642 Gianluca Riccardi 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