Chei în baze de date

O cheie primară identifică unic fiecare rând al unui tabel. În Elevi(id, nume), identificatorii 1 și 2 pot deosebi doi elevi numiți Ana. O cheie externă este o coloană care face trimitere la o cheie din alt tabel; clasa_id poate referi Clase(id).

O cheie primară identifică unic fiecare rând al unui tabel: nu admite duplicate și, în SQL, nu admite valoarea nulă. O cheie poate fi formată dintr-o singură coloană sau din mai multe (cheie compusă). O cheie candidată este o combinație minimă de atribute care identifică unic rândul; dintre cheile candidate se alege una ca primară, iar celelalte pot rămâne garantate prin UNIQUE. O cheie externă este o coloană (sau un grup de coloane) care face trimitere la cheia primară a altui tabel. Mai multe rânduri pot avea aceeași valoare de cheie externă: mai mulți elevi pot referi aceeași clasă.

Exemplul 1 — cheia primară, cheia candidată și cheia externă. Tabelul elevi are cheia primară id, numărul matricol ca a doua cheie candidată (UNIQUE) și clasa_id ca cheie externă către clase. Comenzile următoare adaugă doi elevi cu același nume, apoi încearcă trei inserări incorecte:

CREATE TABLE clase (id integer PRIMARY KEY, denumire text NOT NULL);
CREATE TABLE elevi (id integer PRIMARY KEY, matricol text UNIQUE NOT NULL,
                    nume text NOT NULL, clasa_id integer REFERENCES clase(id));
INSERT INTO clase VALUES (9, 'IX A');
INSERT INTO elevi VALUES (1, 'M100', 'Ana', 9), (2, 'M101', 'Ana', 9);
INSERT INTO elevi VALUES (3, 'M102', 'Dan', 99);
INSERT INTO elevi VALUES (1, 'M103', 'Dan', 9);
INSERT INTO elevi VALUES (4, 'M100', 'Dan', 9);
SELECT COUNT(*) FROM elevi;

Primele două inserări reușesc: doi elevi numiți Ana sunt deosebiți de identificatorii 1 și 2, deci numele nu poate fi cheie primară. A treia este respinsă, pentru că nu există clasa 99. A patra este respinsă, pentru că id=1 există deja. A cincea este respinsă, pentru că matricolul M100 există deja. Interogarea finală întoarce 2.

Exemplul 2 — cheia compusă. Un elev poate participa la mai multe cluburi, iar un club are mai mulți elevi. Tabelul inscrieri(elev_id, club_id) are cheia primară formată din perechea celor două coloane, iar fiecare coloană este cheie externă:

CREATE TABLE cluburi (id integer PRIMARY KEY, denumire text NOT NULL);
CREATE TABLE inscrieri (elev_id integer REFERENCES elevi(id),
                        club_id integer REFERENCES cluburi(id),
                        PRIMARY KEY (elev_id, club_id));
INSERT INTO cluburi VALUES (7, 'Robotică'), (8, 'Șah');
INSERT INTO inscrieri VALUES (1, 7), (1, 8);
INSERT INTO inscrieri VALUES (1, 7);
INSERT INTO inscrieri VALUES (2, 7);
INSERT INTO inscrieri VALUES (5, 7);
SELECT COUNT(*) FROM inscrieri;

Rândurile $(1,7)$ și $(1,8)$ sunt acceptate: elevul 1 participă la două cluburi, deci nici elev_id, nici club_id nu este unic separat, dar perechea lor este unică. A doua inserare a lui $(1,7)$ este respinsă, fiindcă perechea există deja. Perechea $(2,7)$ este acceptată, pentru că elevul 2 și clubul 7 există. Perechea $(5,7)$ este respinsă, pentru că elevul 5 nu există, adică ar încălca integritatea referențială. La final, interogarea întoarce 3.

Exemplul 3 — alegerea cheii primare. Tabelul profesori are două chei candidate: adresa de e-mail, care este unică, și CNP-ul. Ca cheie primară se alege un identificator propriu, id: o adresă de e-mail se poate schimba, iar CNP-ul este o dată personală care nu trebuie copiată în alte tabele ca cheie externă. Cheia candidată rămasă în afara cheii primare este garantată prin UNIQUE. Coloana telefon este tot UNIQUE, dar nu este cheie, pentru că poate lipsi:

CREATE TABLE profesori (id integer PRIMARY KEY, email text UNIQUE NOT NULL,
                        telefon text UNIQUE);
INSERT INTO profesori VALUES (1, 'a@scoala.ro', NULL), (2, 'b@scoala.ro', NULL);
INSERT INTO profesori VALUES (3, 'a@scoala.ro', '0700');
SELECT COUNT(*) FROM profesori;

Primele două rânduri sunt acceptate, deși ambele au telefonul nul: în PostgreSQL, MySQL și SQLite, o restricție UNIQUE admite mai multe valori nule (două valori nule nu sunt considerate egale), pe când SQL Server admite una singură pe coloană. O cheie primară nu admite deloc valoarea nulă în niciunul dintre ele. Exemplul se comportă astfel în PostgreSQL și SQLite; în SQL Server a doua inserare ar fi respinsă. Al treilea rând este respins, pentru că adresa a@scoala.ro există deja. Interogarea întoarce 2. Regula practică: cheia primară trebuie să fie stabilă, adică să nu se modifice după inserare, și cât mai scurtă, fiindcă valoarea ei este repetată în fiecare cheie externă care o referă.

Greșeli frecvente

• Greșit: numele unei persoane este întotdeauna cheie primară potrivită.
  Corect: poate fi repetat.
• Greșit: o cheie externă identifică obligatoriu unic rândul în tabelul care o conține.
  Corect: mai mulți elevi pot referi aceeași clasă.
• Greșit: cheia primară permite două rânduri cu același identificator.
  Corect: unicitatea este obligatorie.

Înainte și după