Primary key or Unique index?

by

What is a unique index?

A unique index on a column is an index on that column that also enforces the constraint that you cannot have two equal values in that column in two different rows. Example:

CREATE TABLE table1 (foo int, bar int);
CREATE UNIQUE INDEX ux_table1_foo ON table1(foo);  -- Create unique index on foo.

INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK
INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails!

Duplicate entry '1' for key 'ux_table1_foo'

The last insert fails because it violates the unique index on column foo when it tries to insert the value 1 into this column for a second time.

In MySQL a unique constraint allows multiple NULLs.

It is possible to make a unique index on mutiple columns.

Primary key versus unique index

Things that are the same:

  • A primary key implies a unique index.

Things that are different:

  • A primary key also implies NOT NULL, but a unique index can be nullable.
  • There can be only one primary key, but there can be multiple unique indexes.
  • If there is no clustered index defined then the primary key will be the clustered index.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to create multiple one to one’s
  3. What are best practices for multi-language database design? [closed]
  4. Strings as Primary Keys in SQL Database [closed]
  5. Are soft deletes a good idea? [duplicate]
  6. Database Design for Revisions?
  7. A beginner’s guide to SQL database design [closed]
  8. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  9. Storing money in a decimal column – what precision and scale? [closed]
  10. single fixed table with multiple columns vs flexible abstract tables
  11. How to design a database for User Defined Fields?
  12. Composite Primary key vs additional “ID” column?
  13. Database EAV Pros/Cons and Alternatives
  14. Max length for client ip address [duplicate]
  15. Name database design notation you prefer and why?
  16. MySQL – how to front pad zip code with “0”?
  17. Use email address as primary key?
  18. Composite Primary Keys : Good or Bad?
  19. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  20. SQL: Using NULL values vs. default values
  21. What does ON [PRIMARY] mean?
  22. What is the best way to implement soft deletion?
  23. Relational Data Model for Double-Entry Accounting
  24. Temporal database design, with a twist (live vs draft rows)
  25. Historical / auditable database
  26. Single or multiple databases
  27. Where is |DataDirectory| defined?
  28. When to use SQL Table Alias
  29. why is null not equal to null false
  30. Procedure or function !!! has too many arguments specified

Leave a Comment