| Author |
What's the difference between an unique index and an unique constraint?
|
|
| Friso Wiskerke 2005-09-19, 11:23 am |
| Hi all,
can anyone tell me what the difference is between an unique (non-clustered)
index and an unique constraint?
And when should I use which one?
TIA
Friso Wiskerke
| |
| David Browne 2005-09-19, 11:23 am |
|
"Friso Wiskerke" <friso@pestaartje.nl> wrote in message
news:eVLtcXSvFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> can anyone tell me what the difference is between an unique
> (non-clustered) index and an unique constraint?
> And when should I use which one?
>
A constraint is a logical, an index is physical. A unique constraint is
actually implemented using a unique index, so practically there is no
difference. It is, however, better form to declare uniqueness using a
constraint, as it better surfaces the design into the schema.
David
| |
| Rick Sawtell 2005-09-19, 11:23 am |
|
"Friso Wiskerke" <friso@pestaartje.nl> wrote in message
news:eVLtcXSvFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> can anyone tell me what the difference is between an unique
> (non-clustered) index and an unique constraint?
> And when should I use which one?
>
> TIA
> Friso Wiskerke
>
In SQL Server, there is really no difference. One is created as a ADD
CONSTRAINT and the other through the CREATE INDEX statement. Functionally,
they are equivalent.
Rick Sawtell
MCT, MCSD, MCDBA
| |
| Jerry Spivey 2005-09-19, 11:23 am |
| Friso,
To add the other poster's comments:
A column with a UNIQUE constraint can be referenced by a column with a
FOREIGN KEY constraint in configuring referential integrity however a column
with just a unique index cannot.
HTH
Jerry
"Friso Wiskerke" <friso@pestaartje.nl> wrote in message
news:eVLtcXSvFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> can anyone tell me what the difference is between an unique
> (non-clustered) index and an unique constraint?
> And when should I use which one?
>
> TIA
> Friso Wiskerke
>
| |
| Kalen Delaney 2005-09-19, 11:23 am |
|
Sorry Jerry... back to class with you. :-)
From BOL:
FOREIGN KEY...REFERENCES
Is a constraint that provides referential integrity for the data in the
column or columns. FOREIGN KEY constraints require that each value in the
column exists in the corresponding referenced column(s) in the referenced
table. FOREIGN KEY constraints can reference only columns that are PRIMARY
KEY or UNIQUE constraints in the referenced table or columns referenced in a
UNIQUE INDEX on the referenced table.
---------------------
As long as uniqueness in the referencing table is guaranteed, either by a
constraint or an index, the foreign key can reference it.
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:eB%23Lz9SvFHA.3548@tk2msftngp13.phx.gbl...
>
> Friso,
>
> To add the other poster's comments:
>
> A column with a UNIQUE constraint can be referenced by a column with a
> FOREIGN KEY constraint in configuring referential integrity however a
> column with just a unique index cannot.
>
> HTH
>
> Jerry
> "Friso Wiskerke" <friso@pestaartje.nl> wrote in message
> news:eVLtcXSvFHA.1252@TK2MSFTNGP09.phx.gbl...
>
>
>
| |
| Jerry Spivey 2005-09-19, 11:23 am |
| Aha...I stand corrected. Newsgroup Instant Replay ...getting the call
right. Thanks Kalen :-)
"Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
news:etbTZOTvFHA.3860@TK2MSFTNGP09.phx.gbl...
>
> Sorry Jerry... back to class with you. :-)
>
> From BOL:
>
> FOREIGN KEY...REFERENCES
>
> Is a constraint that provides referential integrity for the data in the
> column or columns. FOREIGN KEY constraints require that each value in the
> column exists in the corresponding referenced column(s) in the referenced
> table. FOREIGN KEY constraints can reference only columns that are PRIMARY
> KEY or UNIQUE constraints in the referenced table or columns referenced in
> a UNIQUE INDEX on the referenced table.
>
> ---------------------
>
> As long as uniqueness in the referencing table is guaranteed, either by a
> constraint or an index, the foreign key can reference it.
>
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www. solidqualitylearning
.com
>
>
>
> "Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
> news:eB%23Lz9SvFHA.3548@tk2msftngp13.phx.gbl...
>
>
>
| |
| Kalen Delaney 2005-09-19, 1:24 pm |
|
I goofed also... I said 'uniqueness in the referencing table' and should
have said 'uniqueness in the referenced table'.
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:e2YrVTTvFHA.2312@TK2MSFTNGP14.phx.gbl...
>
> Aha...I stand corrected. Newsgroup Instant Replay ...getting the call
> right. Thanks Kalen :-)
>
> "Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
> news:etbTZOTvFHA.3860@TK2MSFTNGP09.phx.gbl...
>
>
>
| |
| Geoff N. Hiten 2005-09-19, 1:24 pm |
| In replicated environments, Unique constraints (and their underlying
indexes) don't get replicated by default. Indexes with a Unique property do
get replicated by default. This can have some nasty performance problems in
subscription databases when an expected index turns up missing.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Friso Wiskerke" <friso@pestaartje.nl> wrote in message
news:eVLtcXSvFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> can anyone tell me what the difference is between an unique
> (non-clustered) index and an unique constraint?
> And when should I use which one?
>
> TIA
> Friso Wiskerke
>
|
|
|
|