MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table

by

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

More Related Contents:

  1. difference between primary key and unique key
  2. Difference between Key, Primary Key, Unique Key and Index in MySQL
  3. SQL Query needed for fetch value from Two identical table but not fully identical table
  4. sql query to modify the selected value from database [closed]
  5. SQL Print the name of all Employees together with the name of their supervisor
  6. How to properly create composite primary keys – MYSQL
  7. Is there a REAL performance difference between INT and VARCHAR primary keys?
  8. Does SQL Server Offer Anything Like MySQL’s ON DUPLICATE KEY UPDATE
  9. How to export SQL Server database to MySQL? [duplicate]
  10. The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
  11. How to transform a MSSQL CTE query to MySQL?
  12. Is there a Profiler equivalent for MySql? [closed]
  13. auto increment primary leaving gaps in counting
  14. When to use SELECT … FOR UPDATE?
  15. Best practices for SQL varchar column length [closed]
  16. Relationship between catalog, schema, user, and database instance
  17. ALTER TABLE to add a composite primary key
  18. How to export data from SQL Server 2005 to MySQL [closed]
  19. MySQL Aggregate Functions without GROUP BY clause
  20. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  21. mysql equivalent data types
  22. What resources exist for Database performance-tuning? [closed]
  23. MySQL InnoDB: autoincrement non-primary key
  24. How to write a (MySQL) “LIMIT” in SQL Server?
  25. How to make a primary key start from 1000?
  26. sql group by versus distinct
  27. Equivalent of MSSQL IDENTITY Column in MySQL
  28. Composite Primary Key performance drawback in MySQL
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment