[MySQL] Vreemde sleutel uit eigen tabel

[Verwijderd]

Ik heb de volgende situatie:

Een tabel met gebruikers en de volgende kolommen:

geb_ID, geb_naam, geb_baas

Nu is geb_baas een vreemde sleutel die verwijst naar geb_ID. De baas van een gebruiker is immers ook een gebruiker.

Ik heb een ON UPDATE cascade en ON DELETE set null. wijzig ik het geb_id van een baas dan zou dit bij alle records waar geb_baas = geb_id moeten worden geüpdate.
Dit werkt echter niet, ik krijg de volgende foutmelding:

code:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`xxxt/test`, CONSTRAINT `test_ibfk_1` FOREIGN KEY (`geb_baas`) REFERENCES `test` (`geb_id`) ON DELETE CASCADE ON UPDATE SET NULL)

Op de mysql site las ik dit:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

Het is dus kennelijk niet mogelijk op deze manier, maar hoe dan wel? Ik heb al geëxperimenteerd met triggers maar kom er niet goed uit wat er fout is aan deze trigger:

code:

CREATE TRIGGER zetbaas AFTER UPDATE ON test
FOR EACH ROW BEGIN
UPDATE test set geb_baas = NEW.geb_id
WHERE geb_baas = OLD.geb_id
END

Ik snap dat het in een koppeltabel kan maar dat vind ik eigenlijk weer zo'n omweg.

Hier de sql export van de tabel:

code:

CREATE TABLE `test` (
  `geb_id` smallint(5) unsigned NOT NULL default '0',
  `geb_naam` varchar(50) NOT NULL,
  `geb_baas` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`geb_id`),
  KEY `geb_baas` (`geb_baas`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Gegevens worden uitgevoerd voor tabel `test`
--

INSERT INTO `test` (`geb_id`, `geb_naam`, `geb_baas`) VALUES
(1, 'Flipje de Vries', NULL),
(2, 'Henk Schoorsteen', 1);

--
-- Beperkingen voor tabel `test`
--
ALTER TABLE `test`
  ADD CONSTRAINT `test_ibfk_1` FOREIGN KEY (`geb_baas`) REFERENCES `test` (`geb_id`) ON DELETE SET NULL ON UPDATE CASCADE;

[curry684]

Semi-offtopic tip: jargontermen als 'foreign key' moet je echt niet vertalen als je wilt dat mensen je begrijpen.

[Verwijderd]

curry684 schreef op donderdag 23 oktober 2008 @ 19:23:
Semi-offtopic tip: jargontermen als 'foreign key' moet je echt niet vertalen als je wilt dat mensen je begrijpen.

Mensen die jargontermen als "foreign key" kennen zullen ook vast wel eens van een "vreemde sleutel" hebben gehoord.

[Verwijderd]

offtopic:
Niet dus, dergelijk jargon wordt nooit vertaald zodat iedereen altijd begrijpt wat je bedoelt.

[whoami]

Verwijderd schreef op donderdag 23 oktober 2008 @ 18:52:
Ik heb de volgende situatie:

Een tabel met gebruikers en de volgende kolommen:

geb_ID, geb_naam, geb_baas

Nu is geb_baas een vreemde sleutel die verwijst naar geb_ID. De baas van een gebruiker is immers ook een gebruiker.

Ik heb een ON UPDATE cascade en ON DELETE set null.

Wat je hier zegt, strookt niet met de definitie die ik zie van je FK constraint; die is nl. ON UPDATE set null, ON DELETE cascade (althans, dat is toch hetgeen ik zie in die foutmelding)

Ik vind het wel vreemd dat MySQL dit niet toelaat; andere DBMS'en doen dit wel (maarja ... da's niet het eerste natuurlijk waar MySQL iets anders doet).

Een ON DELETE SET NULL laat hij volgens de docs wel toe ? Is dat dan bv niet voldoende voor jou ? Of ga je werkelijk de waarde van PK's gaan aanpassen ?

edit:
wat Cheatah ondertussen ook zegt ..

[ Voor 27% gewijzigd door whoami op 23-10-2008 20:15 ]

[Verwijderd]

De grootste FAIL zit in het feit dat je denkt dat je ooit de geb_ID van iemand aan zou moeten passen. Dat moet je simpelweg niet willen. Het is toch een surrogate key? Dan moet hij uniek zijn, en verder niets. Geen reden om te wijzigen dus.

[Verwijderd]

whoami schreef op donderdag 23 oktober 2008 @ 20:11:
[...]
Wat je hier zegt, strookt niet met de definitie die ik zie van je FK constraint; die is nl. ON UPDATE set null, ON DELETE cascade (althans, dat is toch hetgeen ik zie in die foutmelding)

Ik vind het wel vreemd dat MySQL dit niet toelaat; andere DBMS'en doen dit wel (maarja ... da's niet het eerste natuurlijk waar MySQL iets anders doet).

Een ON DELETE SET NULL laat hij volgens de docs wel toe ? Is dat dan bv niet voldoende voor jou ? Of ga je werkelijk de waarde van PK's gaan aanpassen ?

edit:
wat Cheatah ondertussen ook zegt ..

Ja dat was een foutje, was al aangepast.

ON DELETE SET NULL is in principe wel genoeg, het is idd ook geen nette oplossing om ooit iemand z'n gebruikers ID te gaan wijzigen maar zo is het systeem waar ik mee werk ooit gebouwd.

[Verwijderd]

Welke betekenis heeft geb_ID dan in je applicatie dat het ooit nodig is om een surrogate key te wijzigen?
Daar ben ik dan wel erg benieuwd naar.

Ik zou er gewoon ON UPDATE RESTRICT van maken, zoals eigenlijk ook hoort, en zoals dit ook als alternatief wordt geboden door MySQL als je toch stiekem ON UPDATE CASCADE gebruikt. Dat ON DELETE SET NULL is wel helder.

[Verwijderd]

Het is geen surrogate key. De geb_id in mijn database is namelijk gelijk aan het personeelsnummer in een andere applicatie. In principe wijzigt deze nooit maar het komt wel eens voor dat hij in de database verkeerd is ingevoerd. Als deze persoon toevallig dan ook nog "baas" is over meerdere gebruikers is het dus later nooit meer aan te passen, tenzij je later zelf bij de gebruikers de baas_id ook omnummert.

[whoami]

Tja, veranderen en personeelsnummer een attribuut maken en met een surrogate key werken. Zeker als het personeelsnummer kan veranderen (want als je zegt : het veranderd 'in principe niet', dan impliceert dat dat het in sommige gevallen wel verandert).

[Verwijderd]

Dat is een oplossing, het probleem is dan alleen dat in de huidige applicatie het personeelsnummer dus al in geb_baas staat. Die zullen dus omgenummerd moeten worden naar de surrogate keys van die gebruikers, maar daar is wel iets voor te bedenken.

iig bedankt tot zo ver.

[ Voor 196% gewijzigd door Verwijderd op 23-10-2008 21:11 ]

[pedorus]

Tsja, een bug/feature request uit 2006.. Altijd lastig, die standaarden...

offtopic:
"Vreemde sleutel" De vertaling van foreign key lijkt mij refererende sleutel, maar beter is gewoon niet vertalen.

[ Voor 8% gewijzigd door pedorus op 23-10-2008 21:27 ]

[Verwijderd]

Verwijderd schreef op donderdag 23 oktober 2008 @ 20:15:
De grootste FAIL zit in het feit dat je denkt dat je ooit de geb_ID van iemand aan zou moeten passen. Dat moet je simpelweg niet willen. Het is toch een surrogate key? Dan moet hij uniek zijn, en verder niets. Geen reden om te wijzigen dus.

En zelfs als 't geen surrogate key is, primary key fields wijzigen is not done.
Maar @TS: als je aan dit datamodel gebonden bent zou ik me niet om foreign keys bekommeren maar me richten op een trigger on update van dat geb_ID veld.
De trigger die je liet zien ziet er vrij aardig uit, maar ik kan 't niet beoordelen omdat ik geen MySQL draai (qua syntax ziet 't er nogal uit als InterBase/FireBird). Alleen kent InterBase geen 'FOR EACH ROW': de trigger wordt aangesproken voor ieder record dat wordt gewijzigd.