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

Question

Can anyone explain about the purpose of PRIMARY KEY, UNIQUE KEY and KEY, if it is put together in a single CREATE TABLE statement in MySQL?

CREATE TABLE IF NOT EXISTS `tmp` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `uid` varchar(255) NOT NULL,   `name` varchar(255) NOT NULL,   `tag` int(1) NOT NULL DEFAULT '0',   `description` varchar(255),   PRIMARY KEY (`id`),   UNIQUE KEY `uid` (`uid`),   KEY `name` (`name`),   KEY `tag` (`tag`) ) ENGINE=InnoDB AUTO_INCREMENT=1 ; 

How do I convert this query to MSSQL?

Answer 1

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.

Answer 2

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 :).