Received: from mail1.registeredsite.com (mail1.registeredsite.com [64.224.9.10]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0SAhRq62337 for ; Sun, 28 Jan 2001 05:43:28 -0500 (EST) (envelope-from nonsolosoft@diff.org) Received: from mail.diff.org (mail.diff.org [216.2.34.198]) by mail1.registeredsite.com (8.11.1/8.11.1) with ESMTP id f0SAhPn00591 for ; Sun, 28 Jan 2001 05:43:25 -0500 Received: from diff.org [62.10.34.69] by mail.diff.org with ESMTP (SMTPD32-6.04) id A7BBC08A0138; Sun, 28 Jan 2001 05:43:07 -0500 Message-ID: <3A73F5BB.F106BB29@diff.org> Date: Sun, 28 Jan 2001 11:34:35 +0100 From: Ferruccio Zamuner Organization: NonSoLoSoft (Italia) X-Mailer: Mozilla 4.76 [en] (X11; U; FreeBSD 4.2-STABLE i386) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Multicolumn primary keys and multicolumn foreign keys Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/426 X-Sequence-Number: 1102 Hi, I've found a trouble and I've tried to avoid it without success: ------- create table companies ( id serial not null primary key, firm_name text not null, activity text ); create table customers ( id int not null references companies, seller_id int not null references companies, note text, primary key (id,seller_id) ); create table invoices ( seller_id int4 not null references companies, /* who send invoice */ customer_id int4 not null, /* who receive the invoice and pay for it */ invoice_no int4 not null unique, invoice_date date, primary key (seller_id,invoice_no,invoice_date), foreign key (seller_id, customer_id) references customers ); INSERT INTO "companies" ("firm_name","activity") VALUES ('NonSoLoSoft','ASP'); INSERT INTO "companies" ("firm_name","activity") VALUES ('MyFavouriteCustomer','Buy and pay'); INSERT INTO "customers" ("id","seller_id","note") VALUES (2,1,'customer since 1966'); INSERT INTO "invoices" (seller_id,customer_id,invoice_no,invoice_date) values (1,2,1,'now'); ERROR: referential integrity violation - key referenced from invoices not found in customers select * from customers; id | seller_id | note ----+-----------+--------------------- 2 | 1 | customer since 1816 (1 row) ------- Why have I found this ERROR about referential integrity violation, if the record to reference is in the customer table? Thank you in advance, \fer