UNIQUE

De primary key is een unieke index bij creatie. Er kan echter slechts één primary key per tabel worden gedefinieerd en indien je toch op een andere kolom ook een unieke index wil leggen kan je dit door een index te bepalen die uniek is. Je kan meerdere unieke indexen binnen één tabel hebben, maar slechts één primary key. De data zullen dan niet fysiek opgeslagen zijn in volgorde van deze unieke index, maar er zal wel een hulpstructuur bijgehouden worden die voor elke unieke waarde zegt waar je ze kan terugvinden.

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

Er zijn twee manieren om een unieke index te creëren, de eerste bij creatie van de tabel of in het tweede geval nadien.

Bij creatie van de tabel doe je dit als volgt.

CREATE TABLE table_name(
//...
UNIQUE KEY(index_column_1,index_column_2,...) 
);

Deze syntax is algemeen en zegt dat een combinatie van waarden uniek moet zijn. Als je index maar één kolom gebruikt, kan je dit doen met een UNIQUE constraint op die kolom, bijvoorbeeld:

CREATE TABLE table_name(
//...
ColName VARCHAR(100) UNIQUE
);

Voorbeeld

We creëren een tabel met een primary key en een unieke index.

CREATE TABLE IF NOT EXISTS People (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    mail VARCHAR(50) NOT NULL,
    UNIQUE KEY index_unique_mail (mail)
);

Met het commando SHOW INDEXES FROM table_namekan je zien dat de unieke index werd aangemaakt.

SHOW INDEXES FROM People;

Vervolgens voegen wat data toe aan de tabel People.

INSERT INTO People(first_name,last_name,mail)
VALUES
('John','Doe','[e-mail]'),
('Jane','Doe','[e-mail]'),
('John','Roe','[e-mail]'),
('Jane','Roe','[e-mail]')
;

Omdat we op mail een unieke index hebben gelegd is het niet meer mogelijk personen toe te voegen met opgave van een mailadres dat al bestaat. Concreet, wanneer we Jef Doe met als mailadres [e-mail] zouden willen toevoegen, zal dit resulteren in een fout, want het opgegeven mailadres komt reeds voor.

INSERT INTO People(first_name,last_name,mail)
VALUES ('Jef','Doe','[e-mail]');

Wat met NULL-waarden in combinatie met unieke indexen? Zie de officiële documentatie. De waarden zijn dus niet noodzakelijk 100% uniek, dit in tegenstelling tot sommige andere databasesystemen.