[mysql] foreign key doet niets

[Verwijderd]

ik draai mysql versie 4.0.11. Ik gebruik webmin om het volgende statement in te lezen:

CREATE TABLE mail
( mID INT NOT NULL AUTO_INCREMENT,
link VARCHAR(50) NOT NULL,
PRIMARY KEY (mID)
)type=INNODB;

CREATE TABLE user
( uID INT NOT NULL AUTO_INCREMENT,
email VARCHAR(50) NOT NULL,
PRIMARY KEY (uID)
)type=INNODB;

CREATE TABLE IF NOT EXISTS inbox
( mID INT NOT NULL,
uID INT NOT NULL,
isRead ENUM('yes', 'no') NOT NULL,
PRIMARY KEY (mID, uID),
INDEX mail_ind (mID),
FOREIGN KEY (mID) REFERENCES mail(mID) ON DELETE CASCADE
ON UPDATE CASCADE
)type=INNODB;

Nu doet mijn foreign key constraint niets, als ik een "mail" maak en een inbox rij die daarnaar verwijst, en vervolgens de verwezen mail verwijder, dan blijft mijn inbox rij gewoon staan.

In de mysql manual vond ik het volgende voorbeeld dat vanaf versie 3.23.44 zou moeten werken:

CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;

CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;

Ik zie geen verschil behalve namen met wat ik doe. Wie weet waar dit aan ligt??

[ Voor 36% gewijzigd door Verwijderd op 08-07-2004 22:32 . Reden: te vroeg gepost ]

[NMe]

Volgens mij was het probleem met MySQL dat het geen foreign keys kan afdwingen, en dat je het dus zelf moet regelen. Het kan zijn dat dat alleen bij MyISAM tabellen is, maar volgens mij ook bij InnoDB (correct me if I'm wrong). Je zal volgens mij dus zelf de FK checks bij moeten houden.

[twiekert]

foreign keys worden zeker wel afgedwongen, anders zou deze functie wat nutteloos zijn in mysql niet

wat gebeurt er met de row in de inbox table? wat voor waarde heeft de mID kolom op het moment dat je de gerelateerde rij verwijderd in de mail table?

[Verwijderd]

Ik maak in mail een rij met mID "1", daarna maak ik in inbox een rij ook met mID "1". Vervolgens verwijder ik (via webmin) de rij in mail in de hoop dat de rij in inbox ook verdwijnt, maar dat wil dus niet.

Overigens staat dus in de manual dat de INNODB engine wel de constraints kan verwerken. Er staat ook dat die INDEX statement nodig is omdat mysql anders de hele tabel door moet zoeken, maar vanaf versie 4.1.nog iets zou die index automatisch gemaakt moeten worden.

[Verwijderd]

twiekert schreef op 08 juli 2004 @ 23:43:
foreign keys worden zeker wel afgedwongen, anders zou deze functie wat nutteloos zijn in mysql niet

Deze functie is nutteloos in MySQL (althans met MyISAM)! Met InnoDB werkt het wel, maar weinig hosting bedrijven hebben dit geinstalleerd (omdat ze dan licentiekosten moeten gaan betalen).

En als je de tabellen aanmaakt met InnoDB, maar deze is niet beschikbaar dan wordt het automatisch MyISAM. (Controleer na het aanmaken van de tabellen de types. Zijn deze echt InnoDB?)

En ik zou zelf nooit ON DELETE CASCADE gebruiken, omdat dit vrij gevaarlijk is (en ik spreek uit ervaring). Een foreign key gebruik je om een constrain af te dwingen, maar ON DELETE CASCADE maakt deze constrain nutteloos. Waarom dan nog de foreign key gebruiken?

Het zelfde geldt ook voor ON UPDATE CASCADE. Als je dit nodigt hebt dan kloppen je tabellen niet. Een primary key hoort niet veranderlijk te zijn.

-Rémy

[twiekert]

Deze functie is nutteloos in MySQL (althans met MyISAM)! Met InnoDB werkt het wel, maar weinig hosting bedrijven hebben dit geinstalleerd (omdat ze dan licentiekosten moeten gaan betalen).

Voor zover ik weet valt InnoDB gewoon onder de GPL license van MySQL waardoor jje er gebruik van kan maken zolang je je applicatie distributeerd onder de GPL license of je distributeerd het niet

En ik zou zelf nooit ON DELETE CASCADE gebruiken, omdat dit vrij gevaarlijk is (en ik spreek uit ervaring). Een foreign key gebruik je om een constrain af te dwingen, maar ON DELETE CASCADE maakt deze constrain nutteloos. Waarom dan nog de foreign key gebruiken?

Waarom zou je niet? ALs je je constraints op database level ipv applicatie level kan afdwingen waarom zou je na verwijdering van een parent row de gerelateerde rows ook niet laten verwijderen door de database? De functionaliteit is er, zonde van je tijd om dezelfde functies in je applicatie code in te bouwen.

Het zelfde geldt ook voor ON UPDATE CASCADE. Als je dit nodigt hebt dan kloppen je tabellen niet. Een primary key hoort niet veranderlijk te zijn.

Zo gek is dat niet hoor, een primary key hoeft niet altijd een auto increment te zijn

[NMe]

twiekert schreef op 09 juli 2004 @ 09:42:
Voor zover ik weet valt InnoDB gewoon onder de GPL license van MySQL waardoor jje er gebruik van kan maken zolang je je applicatie distributeerd onder de GPL license of je distributeerd het niet

Fout:

Innobase Oy is an OEM (Original Equipment Manufacturer) for MySQL AB, and you have to buy the commercial MySQL+InnoDB licenses from MySQL AB. The product containing both MySQL and InnoDB is called MySQL Pro. 'MySQL Classic' does not contain InnoDB. You can place your order at the MySQL AB order page.

Bron: http://www.innodb.com/licenses.php

twiekert schreef op 09 juli 2004 @ 09:42:
Waarom zou je niet? ALs je je constraints op database level ipv applicatie level kan afdwingen waarom zou je na verwijdering van een parent row de gerelateerde rows ook niet laten verwijderen door de database? De functionaliteit is er, zonde van je tijd om dezelfde functies in je applicatie code in te bouwen.

ON DELETE CASCADE is gewoon linke koek. Als er ook maar dìt niet klopt in je database, dan worden er records verwijderd die helemaal niet weg mogen. En die zijn dan ook onherroepelijk weg... In de meeste gevallen kun je zelfs beter een extra veld toevoegen waarmee je een record logisch verwijdert, in plaats van fysiek.

twiekert schreef op 09 juli 2004 @ 09:42:
Zo gek is dat niet hoor, een primary key hoeft niet altijd een auto increment te zijn

Dus? Je moet een primary key niet veranderen. Een PK dient maar voor één ding: unieke identificatie van je record. Je gaat een PK dus niet veranderen als dat eenmaal bestaat.

[twiekert]

NMe84 schreef op 09 juli 2004 @ 12:09:
[...]

Fout:

[...]

Bron: http://www.innodb.com/licenses.php

3e alinea:

The GNU GPL license, under which both MySQL and InnoDB are published, does not allow without a permission from MySQL AB and Innobase Oy linking of InnoDB and MySQL to a product which you distribute but which does not itself satisfy the GNU GPL license.

en volgens MySQL.com:
MySQL Standard includes the standard MySQL storage engines and the InnoDB storage engine. InnoDB is a transaction-safe, ACID-compliant storage engine with commit, rollback, crash recovery and row-level locking capabilities. This version is for users who want the high-performance MySQL database with full transaction support. MySQL Standard is licensed under the GPL. MySQL Pro is the commercially-licensed version of the server with the same feature-set.

Maar dit gaat offtopic.

TS:

check of de tabellen ook daadwerkelijk InnoDB zijn.

Zoja, welke waarde heeft dan de mID kolom in de inbox table NADAT je het gerelateerde mailrecord hebt verwijderd?

[glashio]

NMe84 schreef op 09 juli 2004 @ 12:09:
ON DELETE CASCADE is gewoon linke koek. Als er ook maar dìt niet klopt in je database, dan worden er records verwijderd die helemaal niet weg mogen. En die zijn dan ook onherroepelijk weg...

Gelukkig wel
Een programeur zit niet op dialoog strings te wachten van "Weet u het zeker j/n ?"
ennuh InnoDB ondersteund Transactions/Rollbacks voordit soort 'kritische' queries

[Verwijderd]

ik kan nu even niet kijken wat de typen van de tabellen zijn. Overigens heb ik dit ook nooit gedaan en nooit echt gezien hoe dit zou moeten. Kun je dit met webmin, of op een andere manier makkelijk ontdekken?

Overigens zijn dit niet de definitieve tabellen, ik ben nog in probeerfase. Maar zeggen dat je niet ON DELETE CASCADE moet gebruiken omdat als er ergens iets mis is, alles mis is vind ik niet echt terecht. Als er ergens anders iets niet pluis is dan kom ik daar liever achter dan dat dat verborgen blijft. En voor het geval het mis gaat, dan heb je altijd nog back-ups.

Ook snap ik niet wat jullie bedoelen met het argument dat een primary key een unieke identifier moet zijn en dus niet mag veranderen. Dat eerste deel snap ik natuurlijk wel, maar in bovenstaande tabellen veranderen er toch nooit keys? Of bedoelen jullie dat er ON UPDATE CASCADE bij staat? In dat geval okee, dat moet natuurlijk restrict worden.

Maar goed, het is dus nog allemaal geexperimenteer... Ik zal als ik de kans heb kijken wat voor type de tabellen in het eggie krijgen.


p.s. de mID in inbox blijft onveranderd, dus "1".

[ Voor 3% gewijzigd door Verwijderd op 09-07-2004 14:22 . Reden: p.s. erbij ]

[twiekert]

Verwijderd schreef op 09 juli 2004 @ 14:21:

p.s. de mID in inbox blijft onveranderd, dus "1".

Dan is je foreign key simpelweg niet actief. on delete heeft cascade (=weg), restrict (niets verwijderen zolang er nog child row(s) zijn) en set null (waarde op null zetten).


voer eens de volgende query uit in een mysql client op de database:

show table status;

of show table status LIKE 'zethierjetabelnaam';

dan zie je de table specs incl. foreign keys.

[ Voor 5% gewijzigd door twiekert op 09-07-2004 14:51 ]

[bigbeng]

Bekijk die specs inderdaad maar eens. Als jij de standaardinstallatie van MySQL hebt doorlopen, dan is InnoDB niet geactiveerd. Helaas hanteert MySQL de gewoonte om geen foutmeldingen te geven, maar gewoon terug te schakelen naar iets dat hij wel kent en dat is dus in geval van tabeldefinities MyISAM. En MyISAM negeert vrolijk je FK definities, wederom zonder foutmeldingen.

[Verwijderd]

Ik heb inderdaad myisam staan voor de tabel types. Zal dus aan het installeren moeten. Bedankt.

[Verwijderd]

twiekert schreef op 09 juli 2004 @ 09:42:
Voor zover ik weet valt InnoDB gewoon onder de GPL license van MySQL waardoor jje er gebruik van kan maken zolang je je applicatie distributeerd onder de GPL license of je distributeerd het niet

twiekert schreef op 09 juli 2004 @ 13:36:
The GNU GPL license, under which both MySQL and InnoDB are published, does not allow without a permission from MySQL AB and Innobase Oy linking of InnoDB and MySQL to a product which you distribute but which does not itself satisfy the GNU GPL license.

De GPL license heeft niets te maken met of je voor de software moet betalen (GPL license vermeldt namelijk niet dat dit kosteloos moet zijn!).

twiekert schreef op 09 juli 2004 @ 13:36:
...MySQL Standard is licensed under the GPL. MySQL Pro is the commercially-licensed version of the server with the same feature-set.

Ja, dus MySQL Pro bevat de 'same feature-set' als MySQL standaard en daarbij ook nog InnoDB.

twiekert schreef op 09 juli 2004 @ 09:42:
Waarom zou je niet? ALs je je constraints op database level ipv applicatie level kan afdwingen waarom zou je na verwijdering van een parent row de gerelateerde rows ook niet laten verwijderen door de database? De functionaliteit is er, zonde van je tijd om dezelfde functies in je applicatie code in te bouwen.

Mijn vraag 'Waarom dan nog de foreign key gebruiken?' was retorisch bedoeld, omdat een foreign key met een ON DELETE CASCADE een gebroken foriegn key constrain is en dus 'zinloos' geworden is.

Wat bedoel ik hiermee? Een foreign key constrain heeft twee constrains:
- elke mail MOET naar een bestaande inbox verwijzen;
- een inbox MAG NIET verwijderd worden zolang er mail verwijst naar de inbox;

Op het moment dat je ON DELETE CASCADE doet wordt de laatste constrain opgeheven. Wat nog veel erger is, het verwijderd ongemerkt records. Door een inbox te verwijderen (=de opdracht die ik de DB geef), worden óók alle mails die naar de inbox verwijzen verwijderd (=een opdracht die ik niet opgeef, maar de DB toch ONGEMERKT toch doet voor mij.)

Het feit dat het ongemerkt gebeurd is het grote gevaar! En deze simpele situatie is het nog wel te overzien, maar zodra het ingewikkelder wordt (denk aan één tabel met meedere foreign keys, honderden tabbellen, meerdere DBA's en programmeurs) dan wordt het gevaarlijk. Helemaal omdat er ongemerkt verwijderd wordt en je dus veel later er achter komt dat de data niet klopt.

En tuurlijk geef ik je gelijk dat je constrains op de database moet afdwingen ipv de applicatie als dit mogelijk is, maar ON DELETE CASCADE heeft niets met constrains te maken. Het is een functie om dingen makkelijker te maken/ dingen te automatiseren. Waarom zou je het niet gebruiken als het mogelijk en 'makkelijker' is? Omdat je door dit 'gemak' een hoop ellende binnenhaalt (en alle ervaren DBA'ers zullen dit be-amen.)

Verwijderd schreef op 09 juli 2004 @ 14:21:
...Maar zeggen dat je niet ON DELETE CASCADE moet gebruiken omdat als er ergens iets mis is, alles mis is vind ik niet echt terecht. Als er ergens anders iets niet pluis is dan kom ik daar liever achter dan dat dat verborgen blijft. En voor het geval het mis gaat, dan heb je altijd nog back-ups.

Maar je komt er niet achter want het woord ongemerkt verwijderd.

En ja, tuurlijk zijn er backups..., maar back-ups zijn een laatste redmiddel en ongewenst. Back-ups terugzetten betekend, 'altijd' verlies van data en dus geld (man-uren, verloren geldtransacties, etc.)

-Rémy

[ Voor 4% gewijzigd door Verwijderd op 09-07-2004 19:39 ]

[Verwijderd]

dit is niet meer on-topic, maar ik wil toch nog even reageren op remy.

De eis van referential integrity is (volgens mijn theorieboek tenminste) dat de waarde voor een foreign key ofwel NULL moet zijn, ofwel moet verwijzen naar een bestaande rij in de tabel waarnaar verwezen wordt.

ON DELETE CASCADE zorgt hiervoor, ON DELETE RESTRICT ook en ON DELETE SET NULL doet dit ook.

Dus er zijn in mijn ogen vier opties:

- geen foreign key, maar hierdoor verlies je referential integrity, oftewel een rij in inbox kan naar een niet-bestaande mail verwijzen,
- SET NULL, maar ik wil geen tuples die naar de "null-mail" verwijzen (SET DEFAULT zou hier een soort van variatie op zijn, de default-mail zou dan eigenlijk de null-mail zijn)
- RESTRICT, maar ik wil mn db toch niet oneindig laten groeien. Dan zou ik dus handmatig de inbox rijen moeten verwijderen voordat ik de mail rij verwijder, maar dit is dus precies wat
- CASCADE doet

Oftewel, ik blijf toch bij CASCADE. En omdat er geen lange keten van constraints zal ontstaan (rijen in inbox zullen in mijn ontwerp nooit automatisch veranderd worden) waarin delete leidt tot delete leidt tot delete enzovoorts, denk ik dat de "onverwachte, onopgemerkte" problemen vrij makkelijk voorkomen zullen worden.

[NMe]

Een soort van samenvatting van eerdere posts uit dit topic:
- ON DELETE CASCADE zorgt ervoor dat er ongemerkt records verwijderd worden uit een tabel
- als er ergens iets niet zou kloppen in je tabel, dan zijn je gegevens meteen weg, tenzij je een backup hebt
- backups zijn een laatste redmiddel, en dien je te allen tijde te vermijden; ze nemen tijd in beslag, en er gaat altijd data verloren

Ik hou dus iets meer ervan om zelf de controle te hebben over wat verwijderd wordt, ook al kost me dat misschien een extra query. Als ik inderdaad erg zeker ben van mijn fabrikaatje, of als het geheel óf niet belangrijk is, óf niet vaak geupdate wordt, dan wil ik nog wel eens ON DELETE CASCADE gebruiken. Maar voor gecompliceerde, grotere projecten hou ik toch graag zelf de controle.