Antwoord.

Geschiedenis van de vraag

De controle op uniciteit van zakelijke sleutels (niet alleen op ID, maar ook op 'natuurlijke' velden) is al langer een onderwerp in bedrijfsapplicaties. Dergelijke sleutels zijn vaak complex (met meerdere kolommen) en staan NULL-waarden toe. Standaard SQL-middelen — UNIQUE CONSTRAINT of unieke indexen — hebben beperkingen: volgens de SQL-standaard worden meerdere rijen waarbij ten minste één van de componenten van de UNIQUE-key gelijk is aan NULL, als uniek beschouwd en schenden ze de beperking niet.

Probleem

De zakelijke logica kan vereisen dat een set kolommen (inclusief toestaan van NULL) gezamenlijk uniek is, maar het gedrag van de SQL-standaard (NULL ≠ NULL) doorbreekt dit scenario. Bijvoorbeeld, we hebben een tabel met een paar kolommen (passport_number, passport_series), en ten minste één van hen kan NULL zijn, maar als de waarden gelijk zijn, moeten we duplicaten verbieden.

Oplossing

In de meeste populaire DBMS-en is de oplossing — controleer uniciteit via een trigger of gebruik partiële indexen met een voorwaarde, of gebruik functies die NULL als gelijke waarden vergelijken (zoals ISNULL of COALESCE). Hier is een voorbeeld voor PostgreSQL met behulp van een unieke index op basis van een expressie:

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(passport_number, ''),
  COALESCE(passport_series, '')
);

Op het niveau van de applicatielogica is het vaak nodig om de controle te dupliceren om duplicaten niet te missen tijdens de invoeging.

Belangrijke kenmerken:

Standaard UNIQUE CONSTRAINT werkt niet als er ten minste één NULL in de sleutel zit.
De oplossing hangt af van de DBMS: partiële indexen, berekende kolommen, triggers.
De controle op uniciteit kan worden toevertrouwd aan de zakelijke logica als het creëren van een juiste index niet mogelijk is.

Misleidende vragen.

Kun je er zeker van zijn dat een unieke index de invoer van duplicaten voorkomt als er nullable-kolommen tussen de kolommen zijn?

Nee, dat kan niet. In SQL is het gedrag met NULL bijzonder: rijen met dezelfde set waarden waarbij ten minste één NULL is, worden door de index als verschillend beschouwd en hun gelijktijdige opslag is toegestaan.

Kunnen verschillende DBMS-en NULL-uniciteit op verschillende manieren implementeren?

Ja, er zijn verschillen tussen hen. Bijvoorbeeld, in Oracle staat een unieke index meerdere rijen met NULL toe, terwijl dit in MS SQL niet het geval is. PostgreSQL kan een partiële index maken via expressies.

Is het mogelijk om het probleem alleen op DDL-niveau en zonder triggers te omzeilen?

In sommige DBMS-en — ja, via expressies, bijvoorbeeld COALESCE. Maar niet altijd, en als er complex gedrag nodig is (NULL moet worden beschouwd als een duplicaat), moet je logische controles of triggers gebruiken.

Typische fouten en anti-patronen

Vertrouwen op de standaard UNIQUE wanneer er NULL is toegestaan in de kolommen.
Geen duplicaten controleren op het niveau van de zakelijke logica.
Triggers maken zonder rekening te houden met prestaties.

Voorbeeld uit het leven

Negatieve case

In een niet-kritische database werd besloten om te vertrouwen op een unieke index op (e-mail, afdeling code), waarbij beide velden NULL toestaan. Dit leidde herhaaldelijk tot duplicaten, waardoor externe integraties naar verwachting faalden.

Voordelen:

Eenvoudige DDL

Nadelen:

Onopvallende duplicaten, inconsistente gegevens, rapportfouten

Positieve case

In PostgreSQL werd de zakelijke sleutel met de velden (paspoortnummer, serie) geïmplementeerd via een index op COALESCE, en bovendien werd er een controle aan de applicatiekant toegevoegd.