CODEWITHSUNDEEP
databasedatatableprimary-key
drsnark
drsnark

Reputation: 3033

Database Table Primary Keys

I was recently developing a .Net MVC web application and send the database schema to a DBA I work with to get the database built on a production DB server. The DBA asked me if I needed primary keys in all my tables. I said yes, for the primary reason, that it is good DB design practice. When I asked why the DBA told me that it is preferred to minimize the number of tables in our organization's database servers with primary keys to conserve resources. Is there some sort of detriment to having primary keys in data tables?

Upvotes: 0

Views: 47

Answers (1)

user1409784
user1409784

Reputation:

When you make a 'join table' the primary keys from each contributing table, form a composite key for the join table. It is then quite possible that this composite key can be indexed.

Inefficient indexing strategies can degrade performance.

An example is the 'InnoDB' engine for MySQL, this is one I work with a lot. With InnoDB every index entry is concatenated with a value of the corresponding primary key. When a query reads a record via a secondary index, this is used with the primary key, to find the record.

So the primary key could effect performance especially if it is something big like a java UUID (128bytes).

Upvotes: 1

Related Questions