MySQL - Meaning of "PRIMARY KEY", "UNIQUE KEY" and "KEY" when used together while creating a table

A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction.

A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no two x and y where x is not y and x == y).

The manual explains it as follows:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

A primary key is a 'special' unique key. It basically is a unique key, except that it's used to identify something.

The manual explains how indexes are used in general: here.

In MSSQL, the concepts are similar. There are indexes, unique constraints and primary keys.

Untested, but I believe the MSSQL equivalent is:

CREATE TABLE tmp (  id int NOT NULL PRIMARY KEY IDENTITY,  uid varchar(255) NOT NULL CONSTRAINT uid_unique UNIQUE,  name varchar(255) NOT NULL,  tag int NOT NULL DEFAULT 0,  description varchar(255),);CREATE INDEX idx_name ON tmp (name);CREATE INDEX idx_tag ON tmp (tag);

Edit: the code above is tested to be correct; however, I suspect that there's a much better syntax for doing it. Been a while since I've used SQL server, and apparently I've forgotten quite a bit :).

Just to add to the other answers, the documentation gives this explanation:

• KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

• A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

• A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

MySQL unique and primary keys serve to identify rows. There can be only one Primary key in a table but one or more unique keys. Key is just index.

for more details you can check http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/mysql_primary_key_vs_unique_key_constraints.php

to convert mysql to mssql try this and see http://gathadams.com/2008/02/07/convert-mysql-to-ms-sql-server/