Problem with some keys

From: cipy(dot)mail(at)gmail(dot)com
To: sfpug(at)postgresql(dot)org
Subject: Problem with some keys
Date: 2012-03-17 09:42:27
Message-ID: CAJaL4WZP-2WkrNuoKncOtpae6degqd0LNbPJn1mb_6g=X=Yuog@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sfpug

*Hi guys,*
*
*
*i'm developing a db but i have some problems with the foreign key.
*
*After i create the following tables:*

create table museo(

nomemuseo char(30) default 'MuseoVeronese',
citta char(20) default 'Verona',
indirizzo varchar(60),
numerotelefono varchar(20),
giornochiusura varchar(10) not null,
prezzoadulti float4 not null default 10.0,
prezzoridotto float4 not null,
sitointernet varchar(100),
primary key(nome,citta),
check(prezzoridotto < prezzoadulti));

create table mostra(

titolo char(30),
museo char(30) default 'MuseoVeronese',
citta char(20) default 'Verona',
inizioesposizione date not null,
fineesposizione date not null,
prezzointero float4,
prezzoridotto float4 DEFAULT 5.0,
primary key(titolo,museo,citta),
foreign key(museo,citta) references museo(nome,citta)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT
);

*i've insert the values for each table*

insert into museo (indirizzo,giornochiusura,prezzoridotto) values('Via
Centro','Mercoledì',5.0);
insert into museo values('MuseoVicenza','Vicenza','Via
Vicenza','0123456','Mercoledì',10.0,7.0);
insert into museo values('MuseoVerona','Verona','Via
Verona','6543210','Martedì',9.0,6.0,'www.museoverona.it');
insert into museo values('MuseoMilano','Milano','Via
Milano','0435344','Venerdì',11.0,5.0);

insert into mostra values('Le opere di
Leonardo','MuseoVerona','Verona','2012-01-05','2012-04-15',6.95);
insert into mostra values('Il genio di Da
Vinci','MuseoMilano','Milano','2012-06-02','2012-08-01',6.50,4);
insert into mostra values('Egitto Tra Mistero e
Realtà','MuseoVicenza','Vicenza','2013-07-07','2013-08-20',13.50,7);

*When i try to do update i have the following error i don't know how to
solve it, i've tried lot of thing that i read on Postgres Mailing List but
nothing to do, *
*can anyone explain me what i'm doing wrong, i've made mistakes?*

update museo set nomemuseo='MuseoFantasma' where citta='Milano';

ERROR: insert or update on table "mostra" violates foreign key constraint
"mostra_museo_fkey"

DETAIL: Key (museo, citta)=(MuseoVeronese , Milano
) is not present in table "museo".

Best Regards

S. Ciprian

--
"La vita è piacevole. La morte è pacifica. E' la transizione che crea dei
problemi." cit. Isaac Asimov

Responses

Browse sfpug by date

  From Date Subject
Next Message Peter Headland 2012-03-17 21:36:35 Re: Problem with some keys
Previous Message Josh Berkus 2012-03-16 16:47:16 Upcoming Meetups: March, April, May