[MySQL] UNIQUE sleutel met NULL

[Keiichi]

In een tabel heb ik een UNIQUE sleutel over 2 kolommen heen gezet, waarbij 1 kolom NULL mag zijn.

Als beide kolommen gevuld zijn, worden duplicaten niet toegelaten.

Maar als ik de ene kolom NULL laat, dan wordt het hele uniciteitsgebeuren overboord gesmeten door MySQL, aldus hieronder. (Gebasseerd op kleine proof-of-concept database)

+----+------+-----------+
| id | uni  | parent_id |
+----+------+-----------+
|  3 | NULL |         1 |
|  5 | NULL |         2 |
|  6 | NULL |         2 |
|  1 |   99 |         1 |
|  2 |   99 |         2 |
+----+------+-----------+

Heb ik iets over het hoofd gezien bij UNIQUE keys of is MySQL gewoon in de fout hier?

[EnnaN]

welke twee van deze kolommen zijn nu uniek dan?

[CodeCaster]

Het gaat om een UNIQUE over de kolommen id en uni neem ik aan? Maar de waarden 1&99 en 2&99 zijn toch uniek?

[ Voor 3% gewijzigd door CodeCaster op 15-05-2009 15:56 ]

[Cartman!]

Is 'by design' volgens deze pagina: http://bugs.mysql.com/bug.php?id=25544

[Dido]

Met hierboven. Ik neem aan dat "id" een van die twee velden is, en die is zelfs in zijn eentje al uniek.

Dus, wat is nu precies je probleem? Wat verwacht je?

[Gonadan]

Als je een index over twee kolommen zet geldt hij voor de combinatie van de kolommen.
Als je wilt dat beide kolommen een eigen index hebben moet je er twee zetten.

[Dido]

Cartman!: jouw link geeft aan dat NULL = NULL en NULL != NULL allebeiu false opleveren. Dat klopt, en dat zou verklaren dat je een sleutel (X, null) twee keer zou kunnen hebben.

Het voorbeeld van de TS heeft echter alleen maar unieke waarden, zelfs als ej (onterecht) null = null zou aannemen.

[Cartman!]

Ik neem overigens aan dat ie uni en parent_id als UNIQUE heeft staan en je dan 2 keer de combinatie NULL/2 krijgt die hij niet wil toestaan vanwege de UNIQUE. Zover ik de bugpagina begrijp bedoelen ze dat NULL niet echt telt als een value en dus voor de UNIQUE in de ene row iets anders is dan de ander, zodoende kan er 2 keer NULL/2 inkomen op die key.

edit: wat een hardlopen, hebben we allemaal geen werk te doen ofzo?

[ Voor 9% gewijzigd door Cartman! op 15-05-2009 15:56 ]

[4VAlien]

Wat Gonadan zegt klopt, mocht je deze twee kolommen echter als primary key willen gebruiken dan is er in principe niets aan de hand met de huidige setup. In dat geval wil je daar misschien nog even wat verder over filosoferen: waarom moeten waardes in beide kolommen uniek zijn? Het lijkt er namelijk op dat de waarde in kolom 1 een autoincrement of pseudo key is. Dan is het beter om de eigenlijke key in de rij (uni?) te gebruiken.

[MeatLoaf]

Dit heeft meer te maken met de definitie van "NULL".

De NULL waarde is niets. En is per definitie altijd ongelijk aan alles, dus NULL is ook ongelijk aan NULL.
Regel 2 en 3 in jouw voorbeeld zijn verschillend aan elkaar en voldoen aan de unieke sleutel.
Het is ook niet raadzaam om nullable kolommen in een unieke sletel op te nemen,

[whoami]

Mja, is in SQL Server ook zo maar eigenlijk zou dit niet mogen.
AFAIK is NULL immers nooit gelijk aan NULL.

code:

SELECT 1 WHERE NULL = NULL

SELECT 1 WHERE NULL != NULL

[Remus]

Keiichi schreef op vrijdag 15 mei 2009 @ 15:47:
In een tabel heb ik een UNIQUE sleutel over 2 kolommen heen gezet, waarbij 1 kolom NULL mag zijn.

Als beide kolommen gevuld zijn, worden duplicaten niet toegelaten.

Maar als ik de ene kolom NULL laat, dan wordt het hele uniciteitsgebeuren overboord gesmeten door MySQL, aldus hieronder. (Gebasseerd op kleine proof-of-concept database)

+----+------+-----------+
| id | uni  | parent_id |
+----+------+-----------+
|  3 | NULL |         1 |
|  5 | NULL |         2 |
|  6 | NULL |         2 |
|  1 |   99 |         1 |
|  2 |   99 |         2 |
+----+------+-----------+

Heb ik iets over het hoofd gezien bij UNIQUE keys of is MySQL gewoon in de fout hier?

Voor zover ik weet is dit gedrag volledig in lijn met de SQL2003 standaard.

[Keiichi]

De UNIQUE Index gaat over 'uni' en 'parent_id'.

Wat ik verwacht is dat de kolomwaardes NULL,1 maar 1x voor kan komen in de tabel.

Zoals ik van Cartman! begrijp zou het bij design zijn, omdat NULL voor MySQL geen donder voorstelt, maar dat het voor mijn wel wat voorstelt.

Voorheen heb ik met een zelfde probleem gezeten, maar gewoon ugly opgelost door een negatieve waarde zoals -1 te gebruiken. In het geval dat ik zo'n kolom als foreign key contraint wil opgeven, moet ik in zo'n parenttabel ook de waarde -1 creeeren.

[remco_k]

Keiichi schreef op vrijdag 15 mei 2009 @ 15:47:
In een tabel heb ik een UNIQUE sleutel over 2 kolommen heen gezet, waarbij 1 kolom NULL mag zijn.

Op welke 2 kolommen zit die unique key?

Verder kan het een oplossing zijn om 0 ipv NULL in de betreffende kolom te schrijven.

[Keiichi]

remco_k schreef op vrijdag 15 mei 2009 @ 16:09:
[...]

Op welke 2 kolommen zit die unique key?

Verder kan het een oplossing zijn om 0 ipv NULL in de betreffende kolom te schrijven.

Zie mijn laatste reactie, we postte tergelijk

[Tukk]

Keiichi schreef op vrijdag 15 mei 2009 @ 15:47:
In een tabel heb ik een UNIQUE sleutel over 2 kolommen heen gezet, waarbij 1 kolom NULL mag zijn.

Als beide kolommen gevuld zijn, worden duplicaten niet toegelaten.

Maar als ik de ene kolom NULL laat, dan wordt het hele uniciteitsgebeuren overboord gesmeten door MySQL, aldus hieronder. (Gebasseerd op kleine proof-of-concept database)

+----+------+-----------+
| id | uni  | parent_id |
+----+------+-----------+
|  3 | NULL |         1 |
|  5 | NULL |         2 |
|  6 | NULL |         2 |
|  1 |   99 |         1 |
|  2 |   99 |         2 |
+----+------+-----------+

Heb ik iets over het hoofd gezien bij UNIQUE keys of is MySQL gewoon in de fout hier?

Ik neem aan dat uni een referentie is naar een andere tabel?

Plaats in die referentietabel een uni met de naam 'GEEN', o.i.d. en gebruik die referentie bij die kolom.

[Keiichi]

Tukk schreef op vrijdag 15 mei 2009 @ 16:13:
[...]

Ik neem aan dat uni een referentie is naar een andere tabel?

Plaats in die referentietabel een uni met de naam 'GEEN', o.i.d. en gebruik die referentie bij die kolom.

'uni' kan inderdaad een referentie zijn (all genoemde voorbeelden zijn even van simpele test tabellen).

Zoals in een eerdere reactie had ik al gezegd dat ik voor een soortgelijkprobleem zoiets toegepast had (maar dan met -1 waardes)

Maar zou dit probleem ook met triggers op te lossen zijn? Alvorens een rij ingevoegd word er als dusdanig gecontroleerd of een combinatie niet bestaat om vervolgens een INSERT of UPDATE te weigeren?

(Ik heb vooralsog geen ervaring met het implementeren van Triggers in MySQL, dus handig voor mij om van te voren weten of het wel zin heeft om daar tijd in te steken )

[remco_k]

Dan kan je net zo goed Allow NULL voor de kolom uni uitschakelen.
Dan bereik je hetzelfde: een insert of update mag uni niet op NULL zetten.

[RobertMe]

Dan kan je net zo goed Allow NULL voor de kolom uni uitschakelen.

Waarschijnlijk wil hij dat NULL wel kan, maar dat NULL dan wel als een waarde behandeld moet worden. Dus uni is niet gevuld, legt nergens een referentie naar, maar bij één parent_id, kan NULL ook maar één keer voorkomen.

De opmerking van MeatLoaf klopt ook, want je kunt niet niets met niets vergelijken, want je hebt niks om te vergelijken, dus is het altijd false.

[winkbrace]

remco_k schreef op vrijdag 15 mei 2009 @ 16:21:
Dan kan je net zo goed Allow NULL voor de kolom uni uitschakelen.
Dan bereik je hetzelfde: een insert of update mag uni niet op NULL zetten.

Inderdaad. Het is verstandig om te kijken of je eerst met constraints iets kunt oplossen voordat je naar de zwaardere trigger grijpt.
Daarbij blijkt MySQL geen unique sleutel over kolommen met NULL waardes te kunnen leggen, dus is jouw "ugly" oplossing van -1 of de constraint van remco_k m.i. prima.

[EnnaN]

Keiichi schreef op vrijdag 15 mei 2009 @ 16:09:
Voorheen heb ik met een zelfde probleem gezeten, maar gewoon ugly opgelost door een negatieve waarde zoals -1 te gebruiken. In het geval dat ik zo'n kolom als foreign key contraint wil opgeven, moet ik in zo'n parenttabel ook de waarde -1 creeeren.

Maar is het niet sowieso raar om in een foreign key een NULL toe te staan?

[RobertMe]

EnnaN schreef op vrijdag 15 mei 2009 @ 16:35:
[...]

Maar is het niet sowieso raar om in een foreign key een NULL toe te staan?

Als je bv. een hiërargische structuur hebt al niet. Een menu kun je met één tabel oplossen, je hebt een item_id, een parent_id, een naam, ... parent_id is een FK naar dezelfde tabel, zodat je submenu's eruit kan halen, maar, hoeft niet altijd gevuld te zijn, dus NULL kan, is parent_id NULL, is het een op zichzelf staand menu (zelfde geld voor een forum, een topic start is een post als alle andere, alleen is het nergens een reactie op), een FK kan dus zeker NULL zijn.

[Alain]

EnnaN schreef op vrijdag 15 mei 2009 @ 16:35:
[...]

Maar is het niet sowieso raar om in een foreign key een NULL toe te staan?

Nee hoor. Een verwijzing kan best naar niks verwijzen.

edit:
hmmz, F5

[ Voor 5% gewijzigd door Alain op 15-05-2009 17:01 ]

[Spinal]

RobertMe schreef op vrijdag 15 mei 2009 @ 16:44:
[...]

Als je bv. een hiërargische structuur hebt al niet. Een menu kun je met één tabel oplossen, je hebt een item_id, een parent_id, een naam, ... parent_id is een FK naar dezelfde tabel, zodat je submenu's eruit kan halen, maar, hoeft niet altijd gevuld te zijn, dus NULL kan, is parent_id NULL, is het een op zichzelf staand menu (zelfde geld voor een forum, een topic start is een post als alle andere, alleen is het nergens een reactie op), een FK kan dus zeker NULL zijn.

Er vanuit gaande dat item_id op 1 begint, kun je parent_id ook op 0 (of op -1 zoals eerder gezegd) zetten en heb je NULL niet nodig. Volgens mij is het hele probleem dan opgelost, of niet?

-edit-
Toch verkeerd gelezen geloof ik...

[ Voor 3% gewijzigd door Spinal op 15-05-2009 17:14 ]

[RobertMe]

ZanderZ schreef op vrijdag 15 mei 2009 @ 17:13:
[...]
Er vanuit gaande dat item_id op 1 begint, kun je parent_id ook op 0 (of op -1 zoals eerder gezegd) zetten en heb je NULL niet nodig. Volgens mij is het hele probleem dan opgelost, of niet?

-edit-
Toch verkeerd gelezen geloof ik...

Het was gewoon een algemeen antwoord op de vraag van EnnaN, dat een FK min of meer per default NOT NULL is/hoort te zijn (hetzelfde als dat een PK automatisch NOT NULL is).

Overgens zou ik zelf nooit voor dummy waardes gaan (want door de FK moet er natuurlijk ook weer een id 0 of -1 zijn), dan los ik het wel met een CHECK constraint op die een error gooid, of een trigger, gewoon ff een select draaien en als er een resultaat is, een exception gooien.

[remco_k]

RobertMe schreef op vrijdag 15 mei 2009 @ 17:28:
of een trigger, gewoon ff een select draaien en als er geen resultaat is, een exception gooien.

... Ja, ik ben nog wakker. Jij had al een biertje op zeker?

Een FK de waarde NULL laten toestaan zou ik zelf niet doen. Van een FK mag je m.i. verwachten dat er een FK is, altijd. Is die er niet, wat moet dat record dan in die tabel? (Normalisatie)

En als je dan in de situatie komt dat je soms geen FK hebt maar wel dat record in die tabel kwijt moet, dan een echt record invoeren in de foreign table.
In die foreign table maak je een extra kolommetje "dummy", een bool en die zet je alleen voor dit record op true, de rest default op false, zodat je altijd in staat bent om dit dummy record te herkennen als zijnde dummy.
Een echt mooie oplossing is het niet, maar "een" oplossing is het wel.

[ Voor 25% gewijzigd door remco_k op 16-05-2009 00:25 ]

[Patriot]

remco_k schreef op zaterdag 16 mei 2009 @ 00:22:
[...]

... Ja, ik ben nog wakker. Jij had al een biertje op zeker?

Een FK de waarde NULL laten toestaan zou ik zelf niet doen. Van een FK mag je m.i. verwachten dat er een FK is, altijd. Is die er niet, wat moet dat record dan in die tabel? (Normalisatie)

En als je dan in de situatie komt dat je soms geen FK hebt maar wel dat record in die tabel kwijt moet, dan een echt record invoeren in de foreign table.
In die foreign table maak je een extra kolommetje "dummy", een bool en die zet je alleen voor dit record op true, de rest default op false, zodat je altijd in staat bent om dit dummy record te herkennen als zijnde dummy.
Een echt mooie oplossing is het niet, maar "een" oplossing is het wel.

Weet je zeker dat je dan niet even naar bed moet gaan? Er is inmiddels al gediscussiëerd over het nut van ee nullable FK, en uit die discussie is gebleken dat het wel degelijk logisch kan zijn. Een dummy creeëren is een mogelijkheid, maar meestal verre van ideaal¹.

Je correctie klopt ook niet, want je wilt een exception gooien als er wél resultaat is. Dat betekend namelijk dat de combinatie al bestaat.

Welterusten

^{1 = als we even uitgaan van een hiërarchische structuur, dan zou je het id van het dummy-record in je code moeten hardcoden, of elke keer moeten zoeken naar het dummy-record. Niet echt een constructie die ik graag zou gebruiken.}

[ Voor 9% gewijzigd door Patriot op 16-05-2009 03:00 ]

[remco_k]

Patriot schreef op zaterdag 16 mei 2009 @ 02:58:
[...]


Weet je zeker dat je dan niet even naar bed moet gaan?

Mja, dat heb ik dus ook even gedaan

Er is inmiddels al gediscussiëerd over het nut van ee nullable FK, en uit die discussie is gebleken dat het wel degelijk logisch kan zijn. Een dummy creeëren is een mogelijkheid, maar meestal verre van ideaal¹.

Je correctie klopt ook niet, want je wilt een exception gooien als er wél resultaat is. Dat betekend namelijk dat de combinatie al bestaat.

Ik had last van een gedachten kronkel, dacht dat de query gebruikt werd voor het checken op het FK record, ipv het checken op een duplicate key. Stupid me.

Ach het was al laat, en nu is het weer vroeg, dus ook deze post zal wel rammelen.

[Remus]

remco_k schreef op zaterdag 16 mei 2009 @ 00:22:
[...]
Een FK de waarde NULL laten toestaan zou ik zelf niet doen. Van een FK mag je m.i. verwachten dat er een FK is, altijd. Is die er niet, wat moet dat record dan in die tabel? (Normalisatie)

En als je dan in de situatie komt dat je soms geen FK hebt maar wel dat record in die tabel kwijt moet, dan een echt record invoeren in de foreign table.
In die foreign table maak je een extra kolommetje "dummy", een bool en die zet je alleen voor dit record op true, de rest default op false, zodat je altijd in staat bent om dit dummy record te herkennen als zijnde dummy.
Een echt mooie oplossing is het niet, maar "een" oplossing is het wel.

NULL is juist bedoeld voor dit soort situaties, zodat je geen dummy record nodig hebt.

Om nog even terug te komen op NULLs in Unique Constraints, de SQL 2003 standaard zegt daarover het volgende (overigens gequote uit een draft, niet de officiele standaard):

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In addition, if the unique constraint was defined with PRIMARY KEY, then it requires that none of the values in the specified column or columns be a null value.

[Verwijderd]

Het helpt om de waarde NULL niet als 'niets' te beschouwen, maar als 'onbekend'. Het is dan wat duidelijker dat je twee NULL-waarden niet met elkaar kan vergelijken. Is de eerste onbekende waarde gelijk aan de tweede onbekende waarde? Hm. Misschien, misschien niet. Idem voor testen op ongelijkheid.