Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -4 down vote favorite

In MySQL (5.1) I am able to create a Index on a field that is type UNIQUE. This ensures that the field contains only unique items.

Is there something equivalent to that feature in SQL Server 2008?

An example of MySQL Table CREATE syntax with UNIQUE Index:

CREATE TABLE `routelist` (
    `RouteID` INT(11) NOT NULL AUTO_INCREMENT,
    `RouteName` VARCHAR(20) NOT NULL,
    `Active` TINYINT(4) NOT NULL DEFAULT '1',
    PRIMARY KEY (`RouteID`),
    UNIQUE INDEX `RouteName` (`RouteName`)
)
COMMENT='3.29.2012'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8;
share|improve this question
3  
Have you done any searching? SQL-Server UNIQUE constraints – ypercube Mar 29 '12 at 20:20
@ypercube Indeed. 2nd hit in Google for "SQL Server UNIQUE Index" – swasheck Mar 29 '12 at 20:24
I was trying to search for SQL server equivalent to MySQL unique index and wasn't getting any good results. – John M Mar 29 '12 at 20:30

2 Answers

up vote 3 down vote accepted

MySQL's syntax is just a shortcut for a create index.

So in SQL Server use:

CREATE UNIQUE INDEX routename ON routelist (routename);

works in MySQL just as well.

share|improve this answer
MySQL's shortcut is to create both a UNIQUE constraint and a UNIQUE index. You can't create only one of the two. – ypercube Mar 29 '12 at 21:32
In SQL-Server, creating a UNIQUE constraint, creates a UNIQUE index, too. But creating an index does not also create a UNIQUE constraint. Off course the Uniqueness is enforced but I think that FKs cannot reference this column in this case. (Have to test that) – ypercube Mar 29 '12 at 21:33
No, my bad. You can add FK, even with only a Unique index defined: Unique index and Foreign Key – ypercube Mar 29 '12 at 21:42
up vote 2 down vote

A note. There is one big difference between UNIQUE constraints in SQL-Server and all other DBMS (MySQL included) and the SQL standard, in which a nullable column with a Unique constraint can have multiple Nulls. But in SQL-Server only one Null is allowed:

Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column.

share|improve this answer
Is there a difference between a unique index and a unique constraint for SQL server (regarding the multiple NULLs) – a_horse_with_no_name Mar 29 '12 at 21:09
I don't think so: UNIQUE Constraint and Unique index – ypercube Mar 29 '12 at 21:26
+1 Good pointer on the difference between SQL server and other RDBMs. – StanleyJohns Mar 30 '12 at 15:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.