UNIQUE van twee kolommen geeft enkele FOREIGN KEY

[webfreakz.nl]

Goedemiddag,

Ik ben bezig met een project en gebruik hiervoor met PHP 5.3.5 en MySQL 5.1.54 op Ubuntu Server 11.04. Vanmiddag voegde ik een tabel toe met onder andere de volgende drie kolommen:

- ID, integer, auto_increment
- Serie_id, integer
- Template_id, integer

Het ID heb ik PRIMARY KEY gemaakt en dat werkt goed. Echter als ik een UNIQUE maak van Serie_id én Template_id dan kan ik hierna alleen voor Serie_id een FOREIGN KEY opgeven. Om van Template_id een FOREIGN KEY te kunnen maken moet ik nog een UNIQUE toevoegen op Template_id. Dit zorgt uiteraard voor problemen in de 'unique-ness'. Ik wil geen unieke waarde binnen een kolom, maar van Serie_id en Template_id.

Wil ik iets wat sowieso niet kan of doe ik iets fout? Is dit een fout, of tekortkoming, van PHPMyAdmin "Version: 4:3.3.10-1" of van MySQL? Zit er ergens een configuratie fout? Zou dit met PostgreSQL wel op te lossen zijn?

[cariolive23]

MySQL (of eigenlijk: innoDB) eist een index op een kolom waar je een foreign key constraint op zet. Een UNIQUE constraint wordt door MySQL (en vele andere databases) geimplementeerd door gebruik te maken van een index, die krijg je dus cadeau. Deze index is dus voor de ene FK bruikbaar binnen MySQL, de andere FK eist een eigen index. En ja, dat is een gebrek van MySQL, niet van PHPMyAdmin of welke andere tool dan ook.

PostgreSQL en andere databases hebben geen eisen m.b.t. indexen wanneer er een foreign key constraint wordt aangemaakt, die kennen deze beperkingen niet. Het gebruik van een index laten ze helemaal aan jou over, jij kunt zien of het wel of niet noodzakelijk is.

Ps. Waarom wil je nog een ID toevoegen? Je kunt ook de PK zetten op serie_id en template_id, die is tenslotte ook al unique.

http://dev.mysql.com/doc/...eign-key-constraints.html

[ Voor 4% gewijzigd door cariolive23 op 19-09-2011 19:42 ]

[webfreakz.nl]

cariolive23 schreef op maandag 19 september 2011 @ 19:42:

Ik weet dat een UNIQUE een type Index is. Ik vind het gewoon vreemd dat als ik een UNIQUE key aanmaak van twee kolommen dat ik dan voor maar één een FK aan kan maken via Relational View.

Uiteraard kan ik nog wel een losse INDEX zetten zodat ik de andere FK kan aanmaken, maar ik had dus niet verwacht dat zoiets nodig zou zijn?

Ps. Waarom wil je nog een ID toevoegen? Je kunt ook de PK zetten op serie_id en template_id, die is tenslotte ook al unique.

http://dev.mysql.com/doc/...eign-key-constraints.html

Serie_id en Template_id zitten samen in een tabel genaamd Type. Een andere tabel genaamd Device maakt weer gebruik van Type. Ik wil maar een enkele key gebruiken om een Type te referencen in de tabel Device.

[cariolive23]

webfreakz.nl schreef op maandag 19 september 2011 @ 21:01:
[...]


Ik weet dat een UNIQUE een type Index is. Ik vind het gewoon vreemd dat als ik een UNIQUE key aanmaak van twee kolommen dat ik dan voor maar één een FK aan kan maken via Relational View.

Uiteraard kan ik nog wel een losse INDEX zetten zodat ik de andere FK kan aanmaken, maar ik had dus niet verwacht dat zoiets nodig zou zijn?

MySQL zit vol met verrassingen, dit is echt niet de enige:
http://sql-info.de/mysql/gotchas.html

[_js_]

Een index over twee kolommen is niet hetzelfde als twee indices over enkele kolommen. Een telefoonboek is geordend op plaats, achternaam en dan op voorletter, je kunt er niet makkelijk in zoeken naar iedereen met de voorletter A. Zo ook met een samengestelde index, alleen de eerste kolom daarvan is direct te doorzoeken, de rest alleen te doorzoeken afhankelijk van de voorgaande kolommen.

Dat er een paar vreemde dingen zitten in MySQL is algemeen bekend, maar hoe samengestelde indices werken is er niet één van. Dus een lijst met problemen in MySQL 4.1 en ouder plaatsen is dan ook nutteloos (vooral aangezien MySQL 5.0 al 10 (!!) jaar geleden werd uitgebracht).

[RobIII]

cariolive23 schreef op maandag 19 september 2011 @ 22:18:
[...]

MySQL zit vol met verrassingen, dit is echt niet de enige:
http://sql-info.de/mysql/gotchas.html

this page deals with issues related to MySQL 4.1 and earlier, not 5.0

Granted, MySQL heeft wat nukken en ik ben allerminst fan van MySQL maar dit artikel ben ik inmiddels meer dan beu. Het stamt uit de prehistorie en inmiddels zijn er een heleboel dingen aangepakt/opgelost.

[cariolive23]

RobIII schreef op maandag 19 september 2011 @ 23:43:
[...]


[...]

Granted, MySQL heeft wat nukken en ik ben allerminst fan van MySQL maar dit artikel ben ik inmiddels meer dan beu. Het stamt uit de prehistorie en inmiddels zijn er een heleboel dingen aangepakt/opgelost.

Toch zijn een groot deel van de genoemde problemen nog steeds actueel, een standaard MySQL-configuratie (80% van de configuraties?) heeft hier namelijk nog steeds last van. Ook met versie 5.5 of 5.6, het is helaas niet anders.

De meest beroerde is nog wel het afkappen van data wanneer het niet in een veld past, kwam ik vorige week weer bij een klant tegen. En ze kunnen dit niet zomaar oplossen, valt de complete applicatie stil. Beter 5% uitval dan twee weken stilliggen om alle bugs in de SQL op te gaan lossen wanneer STRICT wordt toegepast.

Het zijn oude lijken in de kast, maar je komt ze nog steeds tegen. En zolang MySQL backwards compatible wil blijven met standaard instellingen, zul je dit ook houden.

[Voutloos]

cariolive23 schreef op dinsdag 20 september 2011 @ 18:39:
Beter 5% uitval dan twee weken stilliggen om alle bugs in de SQL op te gaan lossen wanneer STRICT wordt toegepast.

Dat zijn dan toch bugs in de code van klant. En strictere modes zijn ook al een jaar of 10 mogelijk, dus je klant is er ook zelf verantwoordelijk voor. Een betere default helpt vooral degenen die zelf niet de moeite nemen om hier over na te denken...

Het zijn oude lijken in de kast, maar je komt ze nog steeds tegen. En zolang MySQL backwards compatible wil blijven met standaard instellingen, zul je dit ook houden.

Remmende voorsprong. Wat mij betreft te conservatief, maar soms is er ook iets voor te zeggen.

Anyway, 'zul je dit ook houden' komt eerder door jou dan door mysql; jij bent degene die het er altijd bij de haren bijtrekt. En daarbij is het soms wat onduidelijk of het behulpzaam of puur ranten is.

[cariolive23]

Voutloos schreef op dinsdag 20 september 2011 @ 20:25:
[...]
Dat zijn dan toch bugs in de code van klant. En strictere modes zijn ook al een jaar of 10 mogelijk, dus je klant is er ook zelf verantwoordelijk voor. Een betere default helpt vooral degenen die zelf niet de moeite nemen om hier over na te denken...

Klopt helemaal, maar vergeet niet dat 99 van de 100 programmeurs geen idee hebben van het bestaan van de verschillende modes. En zij zijn het die de applicatie ontwerpen en schrijven, zeker in de projectjes die langzaam maar zeker steeds groter worden: En dan komt er een DBA die laat zien waar het allemaal fout gaat. Maar ja, om dan eerst drie stappen terug te doen, dat zie je weinig.

Remmende voorsprong. Wat mij betreft te conservatief, maar soms is er ook iets voor te zeggen.

Anyway, 'zul je dit ook houden' komt eerder door jou dan door mysql; jij bent degene die het er altijd bij de haren bijtrekt.

Nou wordt 'ie mooi! Nu is het mijn fout dat MySQL ooit heeft bedacht dat je data gewoon kunt weggooien wanneer iets niet past? De giller van de dag. Grappig om dit zo 's morgens vroeg te lezen, je hebt mij nu al een leuke dag bezorgt, dank je!

En daarbij is het soms wat onduidelijk of het behulpzaam of puur ranten is.

Het is ook vaag: Het is een rant omdat ik schijtziek van wordt en er leukere dingen zijn om te doen, maar gelijktijdig verdien ik hier ook een flinke boterham aan.

[GlowMouse]

MySQL/InnoDB beschermt hier de gebruiker: hij wil een foreign key snel kunnen controleren en vereist daarom een index op die kolom. Ik denk zomaar dat het antwoord van _js_ nieuw is voor TS, en hij anders geen idee had waar performanceproblemen bij een insert vandaan zouden komen.

[webfreakz.nl]

GlowMouse schreef op woensdag 21 september 2011 @ 11:53:
*knip*
.. en hij anders geen idee had waar performanceproblemen bij een insert vandaan zouden komen.

Waar ik mee bezig ben wordt geen super performance verwacht. En als die simpele insert een paar seconden duurt (wat het niet zal doen..) dan is dat totaal geen probleem. Het is niet alsof er dagelijks een paar duizend inserts gedaan gaan worden op deze tabel. Misschien hooguit een paar per dag

[Voutloos]

Zou je nogmaals de definitie van je huidige tabellen willen geven en de FK's uit willen leggen? Want ik zie helemaal geen specifiek dbms probleem, maar hoogstens een probleem in je datamodel.

Of probeer je de FK verkeerd om te leggen of zo? Meerdere rows in je 'nieuwe tabel' (jij zette er geen naam bij) kunnen best naar 1 unieke row in de template tabel verwijzen. Of andersom kan je gewoon naar de PK wijzen.

[ Voor 5% gewijzigd door Voutloos op 21-09-2011 19:58 ]