Thread: Multicolumn primary keys and multicolumn foreign keys

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

On Sun, 28 Jan 2001, Ferruccio Zamuner wrote:

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


there's something wrong with ur foreign key statement, take note ur table
customers has primarykey (id,sellers_id)<< take note the order of the
fields, so ur foreignkey must be foreignkey(customer_id,seller_id) and not
foreignkey(seller_id,customer_id)


regards

richard