pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ferruccio Zamuner <nonsolosoft@diff.org>
To: pgsql-sql@postgresql.org
Subject: Multicolumn primary keys and multicolumn foreign keys
Date: Sun, 28 Jan 2001 11:34:35 +0100
Message-ID: <3A73F5BB.F106BB29@diff.org> (raw)

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:  <unnamed> 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





view thread (2+ messages)  latest in thread

Message-ID: <3A73F5BB.F106BB29@diff.org>
Permalink:  ../3A73F5BB.F106BB29@diff.org/
Also on:    postgresql.org/message-id/3A73F5BB.F106BB29@diff.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: nonsolosoft@diff.org
  Subject: Re: Multicolumn primary keys and multicolumn foreign keys
  In-Reply-To: <3A73F5BB.F106BB29@diff.org>
  References: <3A73F5BB.F106BB29@diff.org>

* 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