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` (
    `RouteName` VARCHAR(20) NOT NULL,
    `Active` TINYINT(4) NOT NULL DEFAULT '1',
    PRIMARY KEY (`RouteID`),
    UNIQUE INDEX `RouteName` (`RouteName`)
  • 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

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.

  • 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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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