Cheie primară sau indice unic? (Programare, Sql, Bază De Date, Proiectarea Bazei De Date)

drizzt a intrebat.

La locul de muncă avem o bază de date mare cu indexuri unice în loc de chei primare și totul funcționează bine.

Proiectez o nouă bază de date pentru un nou proiect și am o dilemă:

În teoria BD, cheia primară este elementul fundamental, asta e OK, dar în proiectele REALE care sunt avantajele și dezavantajele ambelor?

Voi ce folosiți în proiecte?

EDIT: …și ce se întâmplă cu cheile primare și replicarea pe MS SQL server?

Comentarii

  • Există câteva considerații suplimentare discutate aici (deși cu contextul suplimentar al unui index de acoperire) – dba.stackexchange.com/questions/21554/… –  > Por StuartLC.
  • NOTĂ: SQLite este diferit prin faptul că permite ca cheia primară să fie nulă, împotriva standardului comun din cauza unei probleme moștenite. sqlite.org/lang_createtable.html –  > Por bitinn.
15 răspunsuri
Mark Byers

Ce este un index unic?

Un index unic pe o coloană este un index pe acea coloană care aplică, de asemenea, constrângerea că nu puteți avea două valori egale în acea coloană în două rânduri diferite. Exemplu:

CREATE TABLE table1 (foo int, bar int);
CREATE UNIQUE INDEX ux_table1_foo ON table1(foo);  -- Create unique index on foo.

INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK
INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails!

Duplicate entry '1' for key 'ux_table1_foo'

Ultima inserare eșuează deoarece încalcă indexul unic pe coloana foo atunci când încearcă să insereze a doua oară valoarea 1 în această coloană.

În MySQL, o constrângere unică permite mai multe NULL-uri.

Este posibil să se realizeze un index unic pe mai multe coloane.

Cheia primară versus indice unic

Lucruri care sunt identice:

  • O cheie primară implică un index unic.

Lucruri care diferă:

  • O cheie primară implică, de asemenea, NOT NULL, dar un index unic poate fi nul.
  • Nu poate exista decât o singură cheie primară, dar pot exista mai mulți indici unici.
  • Dacă nu este definit niciun indice grupat, atunci cheia primară va fi indicele grupat.

Comentarii

  • Rețineți că un index unic este un index pe o coloană nu este pe deplin corect, deoarece un indice unic sau o cheie primară poate include mai multe coloane. –  > Por Alex Jasmin.
  • @Alexandre Jasmin: Fixat, mulțumesc. Partea referitoare la mai multe coloane este menționată mai târziu. –  > Por Mark Byers.
  • În ceea ce privește valorile nule, standardele ansi permit mai multe valori nule într-un set de date cu o constrângere unică asupra acestuia, iar aceasta este și implementarea pe Oracle și PostgreSQL. Totuși, cred că SQL Server permite doar o singură valoare nulă. –  > Por David Aldridge.
  • dar tot nu am înțeles, cum ar fi când să folosesc cheia primară sau când să folosesc indexul unic? sau poate ambele în aceleași situații. –  > Por Amit.
Filip Ekberg

Poți să o vezi astfel:

O cheie primară ESTE unică

O valoare unică nu trebuie să fie reprezentarea elementului

Adică?; Ei bine, o cheie primară este utilizată pentru a identifica elementul, dacă aveți o „persoană”, ați dori să aveți un număr de identificare personală (SSN sau similar) care este primar pentru persoana dumneavoastră.

Pe de altă parte, persoana ar putea avea un e-mail care este unic, dar care nu o identifică.

Întotdeauna am chei primare, chiar și în tabelele de relații ( tabelul intermediar / tabelul de conexiuni ) s-ar putea să le am. De ce? Ei bine, îmi place să urmez un standard atunci când codific, dacă „Persoana” are un identificator, mașina are un identificator, ei bine, atunci și Persoana -> Mașina ar trebui să aibă un identificator!

Comentarii

  • În tabelele de relații: vă referiți la faptul că introduceți o nouă coloană cu o cheie primară artificială (un număr întreg, de exemplu) sau folosiți o cheie primară compusă (person_id, car_id)? – necunoscut
  • cheia primară (person_id, car_id) ar fi cea mai bună. Dar, în general, eu creez o nouă coloană, sigur că acest lucru generează o anumită suprasolicitare, dar am considerat că este un lucru bun. Nu se știe niciodată dacă vrei să te raportezi la o anumită relație într-un scenariu ulterior. –  > Por Filip Ekberg.
  • Celălalt lucru pe care cheia primară surogat îl face pentru tabelul compozit/unirea dvs. este facilitarea întreținerii sarcinilor manuale. –  > Por Robert C. Barth.
  • Aveți nevoie de o cheie primară doar dacă aveți de gând să aveți copii. De ce să adaugi o coloană și o secvență dacă valoarea nu apare nicăieri, dacă valoarea nu este folosită pentru nimic? Este o muncă de fațadă pentru a împiedica Access să ceară o PK. Creați o PK dacă aveți nevoie să identificați înregistrarea într-un copil, altfel este o risipă. – Mark Brady
  • Dacă nu are nimic de-a face cu relațiile, cu ce are de-a face? Arătați spre un câmp și spuneți că acesta este primar. Și? Și apoi ce se întâmplă? Și dacă nu există un pk natural, adaug o coloană și o secvență și un declanșator și tot pentru că ____? Unele trebuie să fie primare. Eu evit regulile fără motive. – Mark Brady
Jonas Lincoln

Cheile străine funcționează cu constrângeri unice la fel ca și cheile primare. Din Books Online:

O constrângere CHEIE STRĂINĂ nu trebuie să fie legată doar de o constrângere CHEIE PRIMARĂ dintr-un alt tabel; poate fi definită și pentru a face referire la coloanele unei constrângeri UNICE dintr-un alt tabel.

Pentru replicarea tranzacțională, aveți nevoie de cheia primară. Din Books Online:

Tabelele publicate pentru replicarea tranzacțională trebuie să aibă o cheie primară. Dacă o tabelă se află într-o publicație pentru replicare tranzacțională, nu puteți dezactiva niciun index care este asociat cu coloanele cu cheie primară. Acești indici sunt necesari pentru replicare. Pentru a dezactiva un index, trebuie mai întâi să eliminați tabelul din publicație.

Ambele răspunsuri se referă la SQL Server 2005.

Comentarii

  • CEA mă sperie de moarte (primul citat). De ce? Am o tabelă de persoane cu un ID arbitrar care este PK-ul meu, dar decid să adaug un UK la Phone, Email, & SSN… deci acum 4 tabele diferite se alătură persoanei pe 4 coloane diferite? Cred că aș renunța la orice flexibilitate pe care ai putea-o obține pentru consecvență. – Mark Brady
aekeus

Alegerea momentului în care să folosiți o cheie primară surogat, spre deosebire de o cheie naturală, este dificilă. Răspunsurile de genul, întotdeauna sau niciodată, sunt rareori utile. Eu consider că depinde de situație.

Ca exemplu, am următoarele tabele:

CREATE TABLE toll_booths (
    id            INTEGER       NOT NULL PRIMARY KEY,
    name          VARCHAR(255)  NOT NULL,
    ...
    UNIQUE(name)
)

CREATE TABLE cars (
    vin           VARCHAR(17)   NOT NULL PRIMARY KEY,
    license_plate VARCHAR(10)   NOT NULL,
    ...
    UNIQUE(license_plate)
)

CREATE TABLE drive_through (
    id            INTEGER       NOT NULL PRIMARY KEY,
    toll_booth_id INTEGER       NOT NULL REFERENCES toll_booths(id),
    vin           VARCHAR(17)   NOT NULL REFERENCES cars(vin),
    at            TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    amount        NUMERIC(10,4) NOT NULL,
    ...
    UNIQUE(toll_booth_id, vin)
)

Avem două tabele de entități (toll_booths și cars) și o tabelă de tranzacții (drive_through). Site-ul toll_booth utilizează o cheie surogat deoarece nu are un atribut natural care nu este garantat să se schimbe (numele poate fi schimbat cu ușurință). Adresa cars utilizează o cheie primară naturală, deoarece are un identificator unic care nu se schimbă (vin). Adresa drive_through transaction table utilizează o cheie surogat pentru o identificare ușoară, dar are, de asemenea, o constrângere unică asupra atributelor care sunt garantate a fi unice în momentul în care înregistrarea este inserată.

http://database-programmer.blogspot.com conține câteva articole excelente pe această temă.

empi

Cheile primare nu prezintă niciun dezavantaj.

Pentru a adăuga doar câteva informații la răspunsurile lui @MrWiggles și @Peter Parker, atunci când tabelul nu are cheie primară, de exemplu, nu veți putea edita datele în unele aplicații (acestea vor ajunge să spună ceva de genul „nu se pot edita/șterge date fără cheie primară”). Postgresql permite ca mai multe valori NULL să fie în coloana UNIQUE, iar PRIMARY KEY nu permite NULL-uri. De asemenea, unele ORM care generează cod pot avea probleme cu tabelele fără chei primare.

UPDATE:

Din câte știu eu, nu este posibilă replicarea tabelelor fără chei primare în MSSQL, cel puțin fără probleme (detalii).

Comentarii

  • Există un overhead atunci când sunt inserate rânduri noi sau când coloana respectivă este actualizată . – Mark Brady
Ray Hidayat

Dacă ceva este o cheie primară, în funcție de motorul BD, întregul tabel este sortat după cheia primară. Acest lucru înseamnă că căutările sunt mult mai rapide în cazul cheii primare, deoarece nu trebuie să se facă dereferențiere, așa cum se întâmplă cu orice alt tip de index. În afară de asta, este doar o teorie.

Comentarii

  • tabelul va fi sortat în funcție de indicele clusterizat, nu neapărat în funcție de cheia primară. –  > Por Ray Booysen.
  • se întâmplă ca majoritatea oamenilor să își seteze cheia primară ca fiind indicele clusterizat. –  > Por Ray Booysen.
  • Ceea ce știm că este adesea o idee foarte proastă, cu excepția cazului în care ne plac punctele fierbinți și arborii de indici dezechilibrați în tabelele noastre, desigur… –  > Por Mike Woodhouse.
  • Nu este ÎNTOTDEAUNA o idee foarte proastă. Cunoașteți-vă datele, cunoașteți-vă RDBMS-ul, știți ce înseamnă alegerile. Rareori alegerea este întotdeauna bună sau rea. Dacă ar fi fost ÎNTOTDEAUNA una, baza de date ar fi impus-o sau ar fi interzis-o. Vă oferă posibilitatea de a alege pentru că „Depinde”. – Mark Brady
tddmonkey

În plus față de ceea ce au spus celelalte răspunsuri, unele baze de date și sisteme pot să solicite ca un primar să fie prezent. O situație îmi vine în minte; atunci când se utilizează replicarea întreprinderii cu Informix, un PK trebuie să fie prezent pentru ca un tabel să participe la replicare.

Peter Parker

Atâta timp cât nu permiteți NULL pentru o valoare, ar trebui să fie tratate la fel, dar valoarea NULL este tratată diferit în bazele de date(AFAIK MS-SQL nu permite mai mult de o (1) valoare NULL, mySQL și Oracle permit acest lucru, dacă o coloană este UNIQUE)Deci, dvs. trebuie să să definiți această coloană NOT NULL UNIQUE INDEX

Comentarii

  • MS-SQL permite mai multe valori NULL într-o coloană care are un index unic, la fel ca orice alt RDBMS. Gândiți-vă în felul următor: NULL nu este o valoare, deci atunci când introduceți un al doilea NULL, acesta nu se va potrivi niciodată cu unul existent. Expresia (NULL == NULL) nu este evaluată la adevărat sau fals, ci la NULL. –  > Por gregmac.
  • Mulțumesc gregmac, nu eram sigur dacă MS respectă acest lucru. Mi-am amintit de unele ciudățenii MS cu acest lucru, însă cu câțiva ani în urmă (înainte de 2000) și ar putea fi vorba și de un vechi Access-DB. tuse –  > Por Peter Parker.
Walter Mitty

Nu există o cheie primară în teoria datelor relaționale, așa că întrebarea dumneavoastră trebuie să primească un răspuns la nivel practic.

Indicii unici nu fac parte din standardul SQL. Implementarea particulară a unui SGBD va determina care sunt consecințele declarării unui index unic.

În Oracle, declararea unei chei primare va duce la crearea unui index unic în numele dumneavoastră, astfel încât întrebarea este aproape discutabilă. Nu vă pot spune despre alte produse SGBD.

Sunt în favoarea declarării unei chei primare. Acest lucru are ca efect interzicerea NULL-urilor în coloana (coloanele) cheie, precum și interzicerea duplicatelor. De asemenea, sunt în favoarea declarării constrângerilor REFERENCES pentru a impune integritatea entității. În multe cazuri, declararea unui index pe coloana (coloanele) unei chei străine va accelera îmbinările. În general, acest tip de index nu ar trebui să fie unic.

Comentarii

  • O cheie primară în MS SQL Server este întotdeauna atât UNIQUE, cât și NOT NULL – de exemplu, este de fapt doar un index unic, dar cu restricția suplimentară că nu poate fi NULL. –  > Por marc_s.
  • Oracle poate impune o restricție unică cu un index neunic. Aș fi surprins dacă MSSS nu ar putea face acest lucru. A spune că „este de fapt doar un index unic” este un deserviciu. – Mark Brady
  • „În multe cazuri, declararea unui index pe coulmn(s) al unei chei străine va accelera îmbinările.” Acest lucru nu este aproape întotdeauna adevărat într-o lume de stocare a datelor în care îmbinările hash ar fi preferate dacă sunt disponibile. –  > Por JAC2703.
  • OP nu a menționat depozitele. Nu sunt sigur de modul în care funcționează hash loins pe serverul sql. Cât de mult din muncă se poate face la momentul actualizării depozitului. –  > Por Walter Mitty.
Nico Bester

Există unele dezavantaje ale CLUSTERED INDEXES față de UNIQUE INDEXES.

După cum s-a spus deja, un CLUSTERED INDEX ordonează fizic datele din tabel.

Acest lucru înseamnă că, atunci când aveți multe inserții sau ștergeri pe un tabel care conține un index clusterizat, de fiecare dată (ei bine, aproape, în funcție de factorul de umplere) când schimbați datele, tabelul fizic trebuie actualizat pentru a rămâne ordonat.

În cazul tabelelor relativ mici, acest lucru este în regulă, dar când ajungeți la tabele care au date în valoare de GB, iar inserțiile/ștergerile afectează sortarea, veți întâmpina probleme.

Comentarii

  • Care este avantajul, atunci? interogările sortate sunt mai rapide? este mai bine pentru un caz de utilizare în care scrieți majoritatea datelor o singură dată (sau rar) și le interogați tot timpul? –  > Por Buffalo.
HLGEM

Aproape niciodată nu creez un tabel fără o cheie primară numerică. Dacă există, de asemenea, o cheie naturală care trebuie să fie unică, pun și un index unic pe aceasta. Îmbinările sunt mai rapide în cazul numerelor întregi decât în cazul cheilor naturale cu mai multe coloane, iar datele trebuie să se schimbe doar într-un singur loc (cheile naturale tind să trebuiască să fie actualizate, ceea ce nu este un lucru bun atunci când este vorba de relații cheie primară – cheie străină). Dacă veți avea nevoie de replicare, utilizați un GUID în loc de un număr întreg, dar, în cea mai mare parte, prefer o cheie care poate fi citită de utilizator, mai ales dacă acesta trebuie să o vadă pentru a face diferența între John Smith și John Smith.

În puținele cazuri în care nu creez o cheie surogat sunt atunci când am o tabelă de îmbinare care este implicată într-o relație multiplă. În acest caz, declar ambele câmpuri ca fiind cheia primară.

Comentarii

  • „Aproape niciodată nu creez un tabel fără o cheie primară numerică”: de ce întotdeauna numerică? O cheie primară nu trebuie să fie neapărat numerică (apropo, nu trebuie să fie nici AUTO_INCREMENT). –  > Por Hibou57.
  • @Hinou57, pentru că am constatat că cheile naturale rareori sunt de fapt unice și că sunt aproape întotdeauna schimbabile. În plus, îmbinările pe numere întregi sunt, în general, mult mai rapide decât îmbinările pe chei naturale variate sau, mai rău, pe chei compuse. Eu nu le-aș folosi în majoritatea timpului. Acest lucru poate varia în funcție de tipul de informații pe care le stocați în baza de date, dar, din experiența mea personală, am constatat că cheile naturale sunt extrem de nesigure în timp. –  > Por HLGEM.
  • Vă mulțumim pentru răspuns HLGEM. Ce vreți să spuneți prin „nesigure”? Performanță? (Sper că nu este vorba de fiabilitate în sensul de integritate a datelor). Sunt puțin surprins de cuvintele dumneavoastră, deoarece credeam că utilizarea cheilor întregi sau a unor chei mai naturale, cum ar fi VARCHAR scurt, ar face probabil doar o diferență infimă, deoarece hashing-ul este utilizat peste tot, chiar și cu cele mai simple motoare de baze de date. –  > Por Hibou57.
  • Nu sunt fiabile în multe cazuri, deoarece nu sunt unice în mod fiabil, chiar dacă ar trebui să fie. Sunt nesigure pentru că se schimbă, iar acest lucru poate afecta milioane de înregistrări într-o singură actualizare. Aceasta este experiența mea, după ce am văzut și gestionat sau interogat date sau am importat date din sute de baze de date care stochează date despre multe tipuri diferite de informații. –  > Por HLGEM.
Hibou57

Înțelegerea mea este că o cheie primară și un index unic cu o constrângere not-null, sunt același lucru (*); și presupun că se alege una sau alta în funcție de ceea ce specifică sau implică în mod explicit specificația (o chestiune de ceea ce doriți să exprimați și să impuneți în mod explicit). Dacă se cere unicitate și not-null, atunci faceți-o cheie primară. Dacă se întâmplă ca toate părțile unui index unic să nu fie nule, fără nicio cerință în acest sens, atunci faceți din el un index unic.

Singura diferență rămasă este că puteți avea mai mulți indici unici not-null, în timp ce nu puteți avea mai multe chei primare.

(*) Cu excepția unei diferențe practice: o cheie primară poate fi cheia unică implicită pentru anumite operațiuni, cum ar fi definirea unei chei străine. De exemplu, dacă se definește o cheie externă care face referire la un tabel și nu se furnizează numele coloanei, dacă tabelul la care se face referire are o cheie primară, atunci cheia primară va fi coloana la care se face referire. În caz contrar, coloana la care se face referire va trebui să fie numită în mod explicit.

Alții au menționat aici replicarea BD, dar eu nu știu despre asta.

Chirag

Indexul unic poate avea o singură valoare NULL. Creează un INDEX NON-CLUSTERED.Primary Key nu poate conține o valoare NULL. Creează CLUSTERED INDEX.

Markus

În MSSQL, cheile primare ar trebui să fie crescătoare monotonic pentru cea mai bună performanță a indexului clusterizat. Prin urmare, un număr întreg cu inserție de identitate este mai bun decât orice cheie naturală care ar putea să nu fie monoton crescătoare.

Rodney P. Barbati

Dacă ar fi după mine…

Trebuie să satisfaceți cerințele bazei de date și ale aplicațiilor dumneavoastră.

Adăugarea unei coloane de id întreg sau lung cu creștere automată la fiecare tabel pentru a servi drept cheie primară are grijă de cerințele bazei de date.

Apoi adăugați cel puțin un alt index unic la tabel pentru a fi utilizat de aplicația dumneavoastră. Acesta ar fi indexul employee_id, account_id sau customer_id etc. Dacă este posibil, acest indice nu ar trebui să fie un indice compozit.

Aș prefera indicii pe mai multe câmpuri în mod individual în locul indicilor compuși. Baza de date va utiliza indicii pe un singur câmp ori de câte ori clauza where include acele câmpuri, dar va utiliza un indice compozit numai atunci când furnizați câmpurile exact în ordinea corectă – ceea ce înseamnă că nu poate utiliza al doilea câmp într-un indice compozit decât dacă furnizați atât primul cât și al doilea câmp în clauza where.

Sunt de acord cu utilizarea indicilor calculați sau de tip Funcție – și aș recomanda utilizarea acestora în locul indicilor compuși. Este foarte ușor de utilizat indicele de funcție prin utilizarea aceleiași funcții în clauza where.

Acest lucru are grijă de cerințele aplicației dumneavoastră.

Este foarte probabil ca ceilalți indici neprimari să fie de fapt corespondențe ale valorii cheii indexului respectiv cu o valoare a cheii primare, nu cu rowid(). Acest lucru permite efectuarea de operațiuni de sortare fizică și de ștergere fără a fi nevoie să se recreeze acești indici.