-4

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;
Improve this question
3
  • 3
    Have you done any searching? SQL-Server UNIQUE constraints Commented Mar 29, 2012 at 20:20
  • @ypercube Indeed. 2nd hit in Google for "SQL Server UNIQUE Index" Commented Mar 29, 2012 at 20:24
  • I was trying to search for SQL server equivalent to MySQL unique index and wasn't getting any good results. Commented Mar 29, 2012 at 20:30

2 Answers 2

Reset to default
3

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.

Improve this answer
3
  • MySQL's shortcut is to create both a UNIQUE constraint and a UNIQUE index. You can't create only one of the two. Commented Mar 29, 2012 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) Commented Mar 29, 2012 at 21:33
  • No, my bad. You can add FK, even with only a Unique index defined: Unique index and Foreign Key Commented Mar 29, 2012 at 21:42
2

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.

Improve this answer
3
  • Is there a difference between a unique index and a unique constraint for SQL server (regarding the multiple NULLs) Commented Mar 29, 2012 at 21:09
  • I don't think so: UNIQUE Constraint and Unique index Commented Mar 29, 2012 at 21:26
  • +1 Good pointer on the difference between SQL server and other RDBMs. Commented Mar 30, 2012 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.