Când trebuie să folosesc o constrângere unică în loc de un index unic?

Atunci când o coloană să aibă valori distincte, pot folosi fie o constrângere

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

sau pot folosi un index unic

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

Coloane cu constrângeri unic pare a fi bun candidați pentru indicii unice.

Sunt acolo orice motive cunoscute de a utiliza constrângeri unic și să nu utilizeze indicii unice în loc?

Nick Chammas
Întrebarea editată 26 aprilie 2012 в 11:21
Administrarea bazelor de date
best-practices index sql-server unique-constraint constraint
4 ianuarie 2011 в 10:32
23 vizualizări
Comentarii la întrebare (3)
Soluția

Sub capota o constrângere unică este pusă în aplicare în același mod ca un index unic - un index este necesar pentru a îndeplini în mod eficient cerința de a impune constrângeri. Chiar dacă indexul este creat ca un rezultat de o constrângere UNICĂ, interogarea planner poate folosi ca orice alt indice dacă se vede ca cel mai bun mod de a aborda o anumită interogare.

Deci, pentru o bază de date care suportă atât caracteristici de care alegerea de a folosi de multe ori va veni în jos pentru stilul preferat și consistență.

Dacă sunteți de planificare pentru a utiliza indicele ca un index (de exemplu, codul se poate baza pe căutare/sortare/filtrare pe acel teren să fie rapid) mi-ar utiliza în mod explicit un index unic (și comentariu sursa), mai degrabă decât o constrângere de a face clar - în acest fel, dacă unicitatea cerință este schimbat într-o revizuire ulterioară a aplicației (sau alte coder) va ști să asigurați-vă că un non-index unic este pus în loc de unul unic (doar scoate o constrângere unică ar elimina index complet). De asemenea, un anumit indice poate fi numit într-un index indiciu (de exemplu, CU(INDEX(ix_index_name)), care nu't cred că este cazul pentru indicele creat în spatele scenei pentru gestionarea unicitatea cum e puțin probabil să-i știu numele.

De asemenea, dacă aveți doar nevoie pentru a pune în aplicare unicitatea ca o regulă, mai degrabă decât domeniul nevoie să fie căutat sau utilizate pentru sortare, apoi m-am'd folosi constrângere, din nou, pentru a face utilizarea preconizată mult mai evident atunci când cineva se uită la masa ta definiție.

Rețineți că, dacă utilizați atât o constrângere unică și un index unic pe același câmp al bazei de date nu va fi destul de luminos pentru a vedea duplicarea, așa că se va termina cu doi indici care va consuma spațiu suplimentar și încetini rând insertii/actualizări.

David Spillett
Răspuns editat 4 ianuarie 2011 в 12:52
Comentarii (9)

În plus față de punctele în alte răspunsuri, aici sunt unele diferențe esențiale între cele două.

Notă: mesajele De eroare sunt de SQL Server 2012.

Erori

Încălcarea de o constrângere unică întoarce eroare 2627.

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value is (1).
The statement has been terminated.

Încălcarea un index unic întoarce eroare 2601.

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.P1' with unique index 'P1_u'. The duplicate key value is (1).
The statement has been terminated.

Dezactivarea

O constrângere unică nu poate fi dezactivat.

Msg 11415, Level 16, State 1, Line 1
Object 'P1U_pk' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

Dar index unic în urmă o restricție de cheie primară sau o constrângere unică poate fi dezactivat, la fel ca orice index unic. Hat-sfat Brain2000.

ALTER INDEX P1_u ON dbo.P1 DISABLE ;

Notă de obicei de avertizare că dezactivarea un index grupat face datele inaccesibile.

Opțiuni

Constrângeri unic suport opțiuni de indexare ca FILLFACTOR " și " IGNORE_DUP_KEY, deși acest lucru nu't a fost cazul pentru toate versiunile de SQL Server.

Incluse Coloane

Grup indici pot include non-indexate coloane (denumite o acoperire index, aceasta este o mare îmbunătățire a performanțelor). Indicii spatele CHEII PRIMARE și constrângeri UNIC nu poate include coloane. Hat-sfat @ypercube.

Filtrare

O constrângere Unică nu poate fi filtrat.

Un index unic pot fi filtrate.

CREATE UNIQUE NONCLUSTERED INDEX Students6_DrivesLicence_u 
ON dbo.Students6( DriversLicenceNo ) WHERE DriversLicenceNo is not null ;

Constrângeri Cheie Străină

O restricție de Cheie Externă nu poate face referință la un filtrat index unic, deși se poate face referință la un non-filtrat index unic (cred că acest lucru a fost adăugat în SQL Server 2005).

Denumire

Atunci când crearea de constrângere, specificând un nume de constrângere este opțional (pentru toate cele cinci tipuri de constrângeri). Dacă tu nu't specificați un nume apoi MSSQL va genera pentru tine. 

CREATE TABLE dbo.T1 (
    TID int not null PRIMARY KEY
) ;
GO
CREATE TABLE dbo.T2 (
    TID int not null CONSTRAINT T2_pk PRIMARY KEY
) ;

Atunci când crearea de indici, trebuie să specificați un nume.

Hat-sfat @i-o.

Link-uri

http://technet.microsoft.com/en-us/library/aa224827(v=SQL.80).aspx

http://technet.microsoft.com/en-us/library/ms177456.aspx

Greenstone Walker
Răspuns editat 20 iulie 2018 в 1:57
Comentarii (2)

Pentru a cita MSDN ca o sursă de autoritate:

nu Există diferențe semnificative între a crea o constrângere UNICĂ și crearea unui index unic, care este independent de o constrângere. Validarea datelor se produce în același mod, și de optimizare interogare nu face diferența între un index unic creat de o constrângere sau create manual. Cu toate acestea, creând o constrângere UNICĂ pe coloana face obiectivul indice clar... mai mult info aici

Și...

La Motorul de baze de Date creează automat un index UNIC pentru a pune în aplicare unicitatea cerința de constrângere UNICĂ. Prin urmare, dacă un încercați să introduceți un rând duplicat se face, Motorul de baze de Date se întoarce un mesaj de eroare care afirmă constrângere UNICĂ a fost încălcat și nu se adaugă pe rând la masă. Cu excepția cazului în care un cluster de index este specificată în mod explicit, un unic, index de grup este creat de implicit să aplice constrângere UNICĂ... mai mult info aici

Alte în: https://technet.microsoft.com/en-us/library/aa224827%28v=sql.80%29.aspx

Comentarii (0)

Una dintre diferențele majore între o constrângere unică și un index unic este faptul că o restricție de cheie externă pe o altă masă poate referință coloanele care alcătuiesc o constrângere unică. Acest lucru nu este valabil și pentru indicii unice. În plus, unic sunt definite ca parte a ANSI standard, în timp ce indicii nu sunt. În cele din urmă, constrângere unică în considerare să trăiască în domeniul de logică proiectare de baze de date (care pot fi puse în aplicare în mod diferit de către diferite DB motoare), în timp ce indicele este aspectul fizic. Prin urmare, constrângere unică este mai mult declarativ. Am'd prefera constrângere unică în aproape toate cazurile.

Comentarii (4)

În Oracle o diferență majoră este că puteți crea o funcție-index unic, care nu este greu de realizat cu constrângeri unic:

De exemplu

create unique index ux_test on my_table (case when amount != 0 then fk_xyz end);

Deci fk_xyz este doar unic de înregistrare care au valoare != 0`.

Comentarii (1)

Constrângere UNICĂ este de preferat Index UNIC. Când constrângerea nu este unic, aveți nevoie pentru a utiliza un obișnuit sau non index unic. Constrângere este, de asemenea, un alt tip de index. Indicele este folosit pentru acces mai rapid.

Indicii unice pot avea clauze where. De exemplu, puteți crea indici pentru fiecare an în funcție de data coloană

WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'
Shantha Kumara
Răspuns editat 8 februarie 2017 в 9:37
Comentarii (2)