[pgSQL] Foreign key probleempje

Jurgen assaasas

15/04/2008 12:43:00

Ik ben met PGSQL bezig een voorbeeld te maken voor mijn medestudenten die helemaal geen ervaringen hebben met databases en nu stuit ik op een probleem. Ik heb een aantal tabellen die gekoppeld zijn dat werkt allemaal goed met RESTRICT maar nu heb ik een koppel tabel genaamd vakken_studenten deze tabel koppelt vakken aan leerlingen.

Nu wil ik op studenten een FK maken dat alle records uit vakken_studenten worden verwijderd als er een student wordt verwijderd. Nu krijg ik deze melding:

Code (php)

ERROR: there is no unique constraint matching given keys for referenced table "vakken_studenten"

Ik snap zo niet wat deze error in houdt. mijn SQL:

Code (php)

ALTER TABLE pdotest.studenten ADD CONSTRAINT fk_vakken FOREIGN KEY (id) REFERENCES pdotest.vakken_studenten (student_id) ON UPDATE CASCADE ON DELETE CASCADE;

PHP hulp

25/05/2012 10:01:39

Gesponsorde koppelingen:

Beheerder

Joren de Wit

15/04/2008 12:51:00

Je legt de relatie precies verkeerd om. De FK bevindt zich in de vakken_studenten tabel en refereert naar de studenten tabel. Dus:

Code (php)

1
2
3
4
5
6

ALTER TABLE pdotest.vakken_studenten
ADD CONSTRAINT fk_vakken
FOREIGN KEY (student_id)
REFERENCES pdotest.studenten (id)
ON UPDATE CASCADE
ON DELETE CASCADE

Frank -

15/04/2008 12:53:00

Een bestaande key van RESTRICT omzetten naar CASCADE zal nooit deze foutmelding opleveren, de melding heeft namelijk helemaal niets te maken met een unique-constraint.

Jouw FK staat precies verkeerdom, je wilt niet op studenten een FK maken, maar op vakken_studenten die verwijst naar de studenten. Vanuit vakken_studenten ga je verwijzen naar het id van de student en die is uniek. Probleem opgelost.

Heeft verder ook niks met PostgreSQL te maken, gaat in iedere database op deze manier.

Jurgen assaasas

15/04/2008 14:19:00

Dus als ik het goed begrijp maak ik FK's aan op het child-object? Ik wil dus dat als er een student verwijderd wordt, dat dan alle relaties(in dit geval zijn vakken waar hij aan mee doet) worden verwijderd. Ik zal eens kijken, hartelijk dank alvast.

Frank -

15/04/2008 14:23:00

Ja, een child wijst naar de parent, de parent weet nergens van, ontkent in alle toonaarden dat het childs heeft. Een database keert dan ook geen kinderblijslag uit, er valt voor de parent dus niets te winnen. Alleen maar gezeik aan je kop, zit niemand op te wachten. Ontkennen dus.

;)

Jurgen assaasas

15/04/2008 14:24:00

pgFrank schreef op 15.04.2008 14:23:

een parent doet dus alleen iets als een child er om zeurt:P

Beheerder

Joren de Wit

15/04/2008 14:25:00

Quote:

Dus als ik het goed begrijp maak ik FK's aan op het child-object?

Jep, dat klopt. Als je ON DELETE CASCADE gebruikt en je verwijdert het records waar de FK's naar verwijzen, zullen alle records met die FK ook verwijderd worden. Andersom is dat natuurlijk niet het geval, als je een record met een FK verwijdert, zal het record waar de FK naar verwijst gewoon blijven bestaan...

Frank -

15/04/2008 14:29:00

Jurgen schreef op 15.04.2008 14:24:

een parent doet dus alleen iets als een child er om zeurt:P

Nee, zelfs dan niet. Een parent heeft geen enkele relatie met een child, een child heeft een relatie met de parent. Dat is dus enkelvoudig, van de child naar de parent. Nooit andersom.

Met CASCADE zorg je er voor dat de child zijn parent in de gaten houdt en daar zelf vervolgacties op neemt. Verwijder je de parent, zal de child zichzelf ook verwijderen. Met RESTRICT zal een child er voor zorgen dat je een parent niet kan verwijderen, maar dat wordt dus door de child afgedwongen en niet door de parent. Een parent heeft tenslotte geen childs, een child heeft een parent.

Raar maar waar.

Jurgen assaasas

15/04/2008 15:02:00

Als ik de code van Blanche gebruik krijg ik deze melding:

Code (php)

1
2

ERROR: insert or update on table "vakken_studenten" violates foreign key constraint "fk_studenten"
DETAIL: Key (student_id)=(1) is not present in table "studenten".

Hij violate zijn eigen CONSTRAINT? (ik heb de naam wel even veranderd, fk_vakken bestond nl. al).

SQL:

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

--Studenten

CREATE TABLE pdotest.studenten
(
  id serial NOT NULL,
  voornaam character varying,
  achternaam character varying,
  klas_id bigint,
  CONSTRAINT pk_studenten PRIMARY KEY (id),
  CONSTRAINT fk_studenten_klassen FOREIGN KEY (klas_id)
      REFERENCES pdotest.klassen (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
ALTER TABLE pdotest.studenten OWNER TO jurgen;

-- Koppel tabel studenten en vakken

CREATE TABLE pdotest.vakken_studenten
(
  id serial NOT NULL,
  vak_id bigint,
  student_id bigint,
  CONSTRAINT pk_vakken_studenten PRIMARY KEY (id),
  CONSTRAINT fk_vakken FOREIGN KEY (vak_id)
      REFERENCES pdotest.vakken (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
ALTER TABLE pdotest.vakken_studenten OWNER TO jurgen;

Frank -

15/04/2008 15:05:00

In de tabel studenten probeer je wederom een FK aan te maken naar de childs. Dat gaat ook nu niet lukken, zo snel verandert de wereld nu ook weer niet... ;)

De tabel vakken_studenten heeft 2 FK's nodig, eentje naar de studenten en eentje naar de vakken. Het id kun je in deze tabel weggooien, die heb je normaal gesproken niet nodig, een PK op de combinatie van student en vak lijkt mij voldoende.

Jurgen assaasas

15/04/2008 15:09:00

Ja maar een PK is uniek, een student kan dus meerdere vakken hebben, dan heb je toch een unieke sleutel nodig om elk record te identificeren?

Frank -

15/04/2008 15:11:00

Ja, en? Maar 1 student zal 1 vak maar 1x volgen. De combinatie student-vak is dus uniek en dus een uitstekende kandidaat voor een PK.

Gewijzigd op 01/01/1970 01:00:00 door Frank -

Jurgen assaasas

15/04/2008 15:14:00

Daar heb je wel gelijk in. Maar hoe wijzig ik dit dan?

het kan namelijk ook zo zijn:

Code (php)

1
2
3
4
5
6

student_id | vak_id
1        | 2
1        | 3
2        | 2
3        | 1
1        | 1

Edit: Hoe maak ik dus een PK combinatie aan?

Gewijzigd op 01/01/1970 01:00:00 door Jurgen assaasas

Frank -

15/04/2008 15:16:00

Prachtig, wat wil je daar dan aan wijzigen? Ziet er goed uit.

Of wil jij stiekum een waarde toekennen aan de volkomen waardeloze waarde van id? nr. 1 is ouder dan nr. 2 en de volgorde is altijd 1 en dan pas 2! Zo ja, dan ben je fout bezig.

Jurgen assaasas

15/04/2008 15:20:00

pgFrank schreef op 15.04.2008 15:16:

Nee dat is niet nodig, maar in dit idee zit er geen PK op de tabel. Wordt er dan automatisch de combinatie gepakt van de 2 om ze scheiden van de andere records?

Frank -

15/04/2008 15:23:00

Je kunt toch een PK maken op basis van deze 2 kolommen?

CONSTRAINT pk_vakken_studenten PRIMARY KEY (vak_id, student_id),

Jurgen assaasas

15/04/2008 15:25:00

pgFrank schreef op 15.04.2008 15:23:

Je kunt toch een PK maken op basis van deze 2 kolommen?

CONSTRAINT pk_vakken_studenten PRIMARY KEY (vak_id, student_id),

Dankje, dat wist ik namelijk niet!

Edit: Ik heb het gewijzigd zoals je zei, alleen werkt het constraint nog steeds niet, wat ik heb gedaan (ik werk met PGadmin3).

1. Wijzig de tabel vakken_studenten.
2. Voeg constraint toe(secundaire sleutel).
3. naam: fk_studenten_vakken.
4. lokale kolom: student_id.
5. reffereert: studenten.id
6. selecteer bij DELETE en UPDATE beide CASCADE

En vervolgens krijg ik weer de error dat hij zijn eigen constraint overschrijdt.

Gewijzigd op 01/01/1970 01:00:00 door Jurgen assaasas

Frank -

15/04/2008 15:56:00

Doe je ergens iets fout of heb je foute data in de tabellen staan. Dit werkt hier prima:

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

CREATE TABLE studenten
(
  id serial NOT NULL,
  voornaam character varying,
  achternaam character varying,
  klas_id bigint,
  CONSTRAINT pk_studenten PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE vakken
(
  id serial NOT NULL,
  naam TEXT,
  CONSTRAINT pk_vakken PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

-- Koppel tabel studenten en vakken

CREATE TABLE vakken_studenten
(
  vak_id bigint,
  student_id bigint,
  CONSTRAINT pk_vakken_studenten PRIMARY KEY (vak_id, student_id),
  CONSTRAINT fk_vakken FOREIGN KEY (vak_id)
      REFERENCES vakken (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT fk_studenten FOREIGN KEY (student_id)
      REFERENCES studenten (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
)
WITH (OIDS=FALSE);

De schemanaam mag je er zelf weer even inzetten.

Jurgen assaasas

15/04/2008 16:07:00

Dank je Frank, ik had de database al gevuld met wat testresultaten, wellicht dat het daarom fout ging, dank! Ik vrijwel alle mogelijkheden getest en het ziet er naar uit dat hij precies doet wat ik wil.

Gewijzigd op 01/01/1970 01:00:00 door Jurgen assaasas

Frank -

15/04/2008 16:16:00

Offtopic: Wanneer je geen beperking wilt opleggen aan de lengte van een string, kun je gewoon het datatype TEXT gebruiken. Dat heeft verder geen negatieve invloed op bv. geheugengebruik. Zie verder de handleiding. Er past maximaal 1 GB aan data in 1 veld, je hebt dus de nodige ruimte tot je beschikking.

Ben wel benieuwd wat voor voor- of achternaam je hebt wanneer dit 1 GB aan data oplevert... ;)

PHPhulp is een Nederlandstalige PHP community sinds 2002. Je vindt hier PHP tutorials, PHP scripts, PHP boeken en nog veel meer. Alles is geheel gratis!