SQL Server equivalent to MySQL UNIQUE index?

All we need is an easy explanation of the problem, so here it is.

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;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply