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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to sql-server

Examples related to primary-key

Examples related to unique-key