0

Hi I've searched the web and this site for the answer I'm looking for but the answers aren't helping me in my case.

I'm making a database for a shop that has multiple locations. Everything worked until I normalized the database. As you can see here:

create table Locatie (

locatie integer,
adres varchar (70),
plaats varchar (70) not null,


constraint pk_locatie primary key (locatie)); go


create table Voorraad (

artikelnr integer,
voorraadid integer,
aantal integer not null,
locatie integer not null,

constraint pk_artikelnr_aantal primary key (artikelnr, aantal),
constraint fk_Artikel_Voorraad foreign key (artikelnr) references Artikel (artikelnr),
constraint fk_Locatie_Voorraad foreign key (voorraadid) references Locatie (locatie),

constraint uq_voorraadid_locatie unique (voorraadid, locatie)); go

these are ofcourse my parrent tables. But I get the error in the child one:

create table Artikelvoorraad (
artikelnr integer,
voorraadid integer,
aantal integer not null,

constraint pk_artikelnr_voorraadid primary key (artikelnr, voorraadid),
constraint fk_Artikel_Artikelvoorraad foreign key (artikelnr) 
references Artikel (artikelnr),
constraint fk_Voorraad_Artikelvoorraad foreign key (voorraadid) 
references Voorraad (voorraadid, locatie)); go

Is there someone who can point me in the right direction?

Thanks for reading this far!

TimB
  • 5
  • 5

1 Answers1

0

Problem is in this one

...
constraint fk_Voorraad_Artikelvoorraad foreign key (voorraadid) 
references Voorraad (voorraadid, locatie)
...

You see that you try to reference one field from table Artikelvoorraad to two fields in the table Voorraad. You probably want to change it to

...
constraint fk_Voorraad_Artikelvoorraad foreign key (voorraadid) 
references Voorraad (voorraadid)
...

But again, your table Voorraad has a strange primary key - why amount is also in the key? If the key is correct, then probably you want this:

constraint fk_Voorraad_Artikelvoorraad foreign key (voorraadid, aantal) 
references Voorraad (voorraadid, aantal)
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Thanks for your answer! But Voorraad has a unique key and by combining it I thought to solve it. Only now I got a problem I had before this error – TimB Mar 14 '15 at 14:44
  • No unique index found for the referenced field of the primary table. So that is why I combined the two. – TimB Mar 14 '15 at 14:47
  • Thanks for you help. I've put amount in the primary key because the amount is every time different from the other stores. But now I see a problem with that yes. If amount would be 21 at a certain time. It can never be 21 again. Well I'm still back at where I began. But thank you for your help! – TimB Mar 15 '15 at 18:13