Unique Constraint vs. Unique Index

[rrhandle]

Can someone explain the difference between a Unique Constraint and a Unique Index, and which should be used when?

Thank you

 

[NoCoolHandle]

A Unique Constraint uses a Unique Index to inforce Uniqueness.

IT just returns a slightly different error and as a constraint might be easier to document.....

For what that is worth


Rob

 

[SQLDenis]

this is your main difference:
UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values


When to use one over the other?
Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of:

A column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.

A column that allows null values.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

For SQL Server both do the same. Create unique constraint, and unique index will be created. Create unique index, no dupes will be allowed on indexed column(s). The only difference is semantical - "constraint" is more logical term, while "index" is it's physical implementation.

For some relational databases (early Watcom/Sybase comes to mind) that distinction made sense. There you could create unique constraint without index.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[NoCoolHandle]

Von
ill bite

<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>

???

 

[vongrunt]

Ya know what is that? :X

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

replace <B> and <P> with spaces and what do you see?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/