SQL – many-to-many table primary key

by

With a simple two-column many-to-many mapping, I see no real advantage to having a surrogate key. Having a primary key on (col1,col2) is guaranteed unique (assuming your col1 and col2 values in the referenced tables are unique) and a separate index on (col2,col1) will catch those cases where the opposite order would execute faster. The surrogate is a waste of space.

You won’t need indexes on the individual columns since the table should only ever be used to join the two referenced tables together.

That comment you refer to in the question is not worth the electrons it uses, in my opinion. It sounds like the author thinks the table is stored in an array rather than an extremely high performance balanced multi-way tree structure.

For a start, it’s never necessary to store or get at the table sorted, just the index. And the index won’t be stored sequentially, it’ll be stored in an efficient manner to be able to be retrieved quickly.

In addition, the vast majority of database tables are read far more often than written. That makes anything you do on the select side far more relevant than anything on the insert side.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. Auto Increment after delete in MySQL
  3. Strings as Primary Keys in SQL Database [closed]
  4. Foreign key referring to primary keys across multiple tables?
  5. How can I define a composite primary key in SQL?
  6. Is there AUTO INCREMENT in SQLite?
  7. Improving performance of cluster index GUID primary key
  8. Does ‘Select’ always order by primary key?
  9. SQL – Inserting a row and returning primary key
  10. adding primary key to sql view
  11. sql swap primary key values
  12. Retrieve Oracle last inserted IDENTITY
  13. Store and reuse value returned by INSERT … RETURNING
  14. The most efficient way to UPPER CASE two columns in a single query [closed]
  15. SQL query to get rows by comparing data in different columns
  16. How to calculate age (in years) based on Date of Birth and getDate()
  17. Does the join order matter in SQL?
  18. Case insensitive searching in Oracle
  19. Is there a shortcut for SELECT * FROM?
  20. sql statement error: “column .. does not exist”
  21. How to get difference between two rows for a column field?
  22. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  23. What datatype to use when storing latitude and longitude data in SQL databases? [duplicate]
  24. Comparing with date in Oracle sql
  25. HAVING without GROUP BY
  26. IN Clause with NULL or IS NULL
  27. SQL selecting rows by most recent date with two unique columns
  28. Insert Update stored proc on SQL Server
  29. is it possible to select EXISTS directly as a bit?
  30. What is the difference between a stored procedure and a view?

Leave a Comment