What’s the best practice for primary keys in tables? [closed]

by

I follow a few rules:

  1. Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache you will use.
  2. Primary keys should never change. Updating a primary key should always be out of the question. This is because it is most likely to be used in multiple indexes and used as a foreign key. Updating a single primary key could cause of ripple effect of changes.
  3. Do NOT use “your problem primary key” as your logic model primary key. For example passport number, social security number, or employee contract number as these “natural keys” can change in real world situations. Make sure to add UNIQUE constraints for these where necessary to enforce consistency.

On surrogate vs natural key, I refer to the rules above. If the natural key is small and will never change it can be used as a primary key. If the natural key is large or likely to change I use surrogate keys. If there is no primary key I still make a surrogate key because experience shows you will always add tables to your schema and wish you’d put a primary key in place.

More Related Contents:

  1. Solutions for INSERT OR UPDATE on SQL Server
  2. How to create multiple one to one’s
  3. Querying data by joining two tables in two database on different servers
  4. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  5. T-SQL Cast versus Convert
  6. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  7. How Can I use “Date” Datatype in sql server?
  8. How to replace a string in a SQL Server Table Column
  9. What is the difference between using a cross join and putting a comma between the two tables?
  10. Pivot Table and Concatenate Columns
  11. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  12. How to run a stored procedure in sql server every hour?
  13. Splitting the string in sql server
  14. Composite Primary key vs additional “ID” column?
  15. Why do you create a View in a database?
  16. Need to list all triggers in SQL Server database with table name and table’s schema
  17. Logging ALL Queries on a SQL Server 2008 Express Database?
  18. How can I do a BEFORE UPDATED trigger with sql server?
  19. Deferrable Constraints in SQL Server
  20. Know relationships between all the tables of database in SQL Server
  21. Can SQL Server Express LocalDB be connected to remotely?
  22. How do I create and query linked database servers in SQL Server?
  23. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  24. Suggestions for implementing audit tables in SQL Server?
  25. SQL Server search using like while ignoring blank spaces
  26. What does ON [PRIMARY] mean?
  27. How do I list all the columns in a table?
  28. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  29. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  30. Space used by nulls in database

Leave a Comment