In SQL Server 2005+ (I use both), does adding the UNIQUE
constraint to a column automatically create an index, or should I still CREATE INDEX
?
See this MSDN article:
The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint.
If you do create an index, you'll end up with two indexes, as this example demonstrates:
create table TestTable (id int)
alter table TestTable add constraint unique_id unique (id)
create unique index ix_TestTable_id on TestTable (id)
select * from sys.indexes where [object_id] = object_id('TestTable')
This will display two unique indexes on TestTable; and the HEAP that represents the table itself.
Yes, it does.
In fact, you can even create a CLUSTERED UNIQUE CONSTRAINT
:
ALTER TABLE mytable ADD CONSTRAINT UX_mytable_col1 UNIQUE CLUSTERED (col1)
, which will make the table to be clustered on col1
.
Almost all databases create an index for UNIQUE CONSTRAINT
, otherwise it would be very hard to maintain it.
Oracle
doesn't even distinguish between UNIQUE CONSTRAINT
and UNIQUE INDEX
: one command is just a synonym for another.
The only difference in Oracle
is that a UNIQUE INDEX
should have a user-supplied name, while a UNIQUE CONSTRAINT
may be created with a system-generated name:
ALTER TABLE mytable MODIFY col1 UNIQUE
This will create an index called SYS_CXXXXXX
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With