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 901AB9DC9A0 for ; Wed, 14 Dec 2005 06:24:14 -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 30148-07-5 for ; Wed, 14 Dec 2005 06:24:16 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp2.libero.it (smtp2.libero.it [193.70.192.52]) by postgresql.org (Postfix) with ESMTP id 577349DC9BE for ; Wed, 14 Dec 2005 06:24:12 -0400 (AST) Received: from localhost (172.16.1.10) by smtp2.libero.it (7.0.027-DD01) id 439D90690033C102 for pgsql-sql@postgresql.org; Wed, 14 Dec 2005 11:24:13 +0100 Received: from smtp1.libero.it ([172.16.1.97]) by localhost (asav10.libero.it [193.70.192.88]) (amavisd-new, port 10024) with ESMTP id 19703-10-5 for ; Wed, 14 Dec 2005 11:24:12 +0100 (CET) Received: from [172.16.0.5] (ppp-103-202.24-151.libero.it [151.24.202.103]) by smtp1.libero.it (Postfix) with ESMTP id 87D1DA8C4A for ; Wed, 14 Dec 2005 11:24:12 +0100 (MET) Message-ID: <439FF32E.7030004@moonwatcher.it> Date: Wed, 14 Dec 2005 11:25:50 +0100 From: Gianluca Riccardi User-Agent: Mozilla/5.0 (X11; U; Linux i686; it-IT; rv:1.7.8) Gecko/20050927 Debian/1.7.8-1sarge3 X-Accept-Language: it MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: DB design and foreign keys References: <439F0FE8.1050503@moonwatcher.it> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned: with antispam and antivirus automated system at libero.it X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/115 X-Sequence-Number: 23657 Jaime Casanova wrote: > [...unnecesary...] > > >> 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) >> > > ^^^^^^^^^^^^^^^^^^^ > > >> ); >> > > [...unnecesary...] > > >> CREATE TABLE order_items ( >> id serial, >> order_code integer REFERENCES orders (order_code) NOT NULL, >> > > > ^^^^^^^^^^^^^^ > [...unnecesary...] > > >> ERROR: there is no unique constraint matching given keys for referenced >> table "orders" >> > > > this is because the PK in the orders table has two fields not one... > so it founds no unique index on orders(order_code) > > > then, what would you suggest, to remove the primary key from the 'id' column? or to remove the 'id' column at all? > > > -- > Atentamente, > Jaime Casanova > (DBA: DataBase Aniquilator ;) > > thank you for your response, regards, Gianluca Riccardi