Should each and every table have a primary key?

by

Short answer: yes.

Long answer:

  • You need your table to be joinable on something
  • If you want your table to be clustered, you need some kind of a primary key.
  • If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records?

In MySQL, the InnoDB storage engine always creates a primary key if you didn’t specify it explicitly, thus making an extra column you don’t have access to.

Note that a primary key can be composite.

If you have a many-to-many link table, you create the primary key on all fields involved in the link. Thus you ensure that you don’t have two or more records describing one link.

Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in a unique index.

And since any primary key involves creating a unique index, you should declare it and get both logical consistency and performance.

See this article in my blog for why you should always create a unique index on unique data:

P.S. There are some very, very special cases where you don’t need a primary key.

Mostly they include log tables which don’t have any indexes for performance reasons.

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. What’s the difference between identifying and non-identifying relationships?
  3. Can I have multiple primary keys in a single table?
  4. How are super- and subtype relationships in ER diagrams represented as tables?
  5. What are database normal forms and can you give examples? [closed]
  6. Difference between scaling horizontally and vertically for databases [closed]
  7. NoSql vs Relational database
  8. Best practices for storing postal addresses in a database (RDBMS)?
  9. Techniques for database inheritance?
  10. What is the most efficient way to store tags in a database?
  11. Microservices with shared database? using multiple ORM’s? [closed]
  12. Why use a 1-to-1 relationship in database design?
  13. What is the optimal length for an email address in a database?
  14. What are the advantages of using a single database for EACH client?
  15. Database optimization orders
  16. How should international geographical addresses be stored in a relational database?
  17. In what way does denormalization improve database performance?
  18. Still Confused About Identifying vs. Non-Identifying Relationships
  19. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  20. Alternatives to Entity-Attribute-Value (EAV)?
  21. have address columns in each table or an address table that is referenced by the other tables?
  22. Minimum no of tables that exists after decomposing relation R into 1NF?
  23. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  24. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  25. Limit an sqlite Table’s Maximum Number of Rows
  26. When we need to use 1-to-1 relationship in database design?
  27. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment