pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Multicolumn primary keys and multicolumn foreign keys
2+ messages / 2 participants
[nested] [flat]

* Multicolumn primary keys and multicolumn foreign keys
@ 2001-01-28 10:34 Ferruccio Zamuner <nonsolosoft@diff.org>
  2001-01-29 03:18 ` Re: Multicolumn primary keys and multicolumn foreign keys chard <chard@hindang.msuiit.edu.ph>
  0 siblings, 1 reply; 2+ messages in thread

From: Ferruccio Zamuner @ 2001-01-28 10:34 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Multicolumn primary keys and multicolumn foreign keys
  2001-01-28 10:34 Multicolumn primary keys and multicolumn foreign keys Ferruccio Zamuner <nonsolosoft@diff.org>
@ 2001-01-29 03:18 ` chard <chard@hindang.msuiit.edu.ph>
  0 siblings, 0 replies; 2+ messages in thread

From: chard @ 2001-01-29 03:18 UTC (permalink / raw)
  To: Ferruccio Zamuner <nonsolosoft@diff.org>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-01-29 03:18 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-28 10:34 Multicolumn primary keys and multicolumn foreign keys Ferruccio Zamuner <nonsolosoft@diff.org>
2001-01-29 03:18 ` chard <chard@hindang.msuiit.edu.ph>

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