About Vlad Mihalcea

Vlad Mihalcea is a software architect passionate about software integration, high scalability and concurrency challenges.

Database primary key flavors

Types of primary keys

All database tables must have one primary key column. The primary key uniquely identifies a row within a table therefore it’s bound by the following constraints:

  • UNIQUE
  • NOT NULL
  • IMMUTABLE

When choosing a primary key we must take into consideration the following aspects:
 

  • the primary key may be used for joining other tables through a foreign key relationship
  • the primary key usually has an associated default index, so the more compact the data type the less space the index will take
  • a simple key performs better than a compound one
  • the primary key assignment must ensure uniqueness even in highly concurrent environments

When choosing a primary key generator strategy the options are:

  1. natural keys, using a column combination that guarantees individual rows uniqueness
  2. surrogate keys, that are generated independently of the current row data

Natural keys

Natural keys’ uniqueness is enforced by external factors (e.g. person unique identifiers, social security numbers, vehicle identification numbers).

Natural keys are convenient because they have an outside world equivalent and they don’t require any extra database processing. We can therefore know the primary key even before inserting the actual row into the database, which simplifies batch inserts.

If the natural key is a single numeric value the performance is comparable to that of surrogate keys.

For compound keys we must be aware of possible performance penalties:

  • compound key joins are slower than single key ones
  • compound key indexes require more space than their single key counterparts

Non-numerical keys are less efficient than numeric ones (integer, bigint), for both indexing and joining. A CHAR(17) natural key (e.g. vehicle identification number) occupies 17 bytes as opposed to 4 bytes (32 bit integer) or 8 bytes (64 bit bigint).

The initial schema design uniqueness assumptions may not forever hold true. Let’s say we’d used one specific country citizen numeric code for identifying all application users. If we now need to support other countries that don’t have such citizen numeric code or the code clashed with existing entries, than we can conclude that the schema evolution is possibly hindered.

If the natural key uniqueness constraints change it’s going to be very difficult to update both the primary keys (if we manage to drop the primary key constraints anyway) and all associated foreign key relationships.

Surrogate keys

Surrogate keys are generated independently of the current row data, so the other column constraints may freely evolve according to the application business requirements.

The database system may manage the surrogate key generation and most often the key is of a numeric type (e.g. integer or bigint), being incremented whenever there is a need for a new key.

If we want to control the surrogate key generation we can employ a 128-bit GUID or UUID. This simplifies batching and may improve the insert performance since the additional database key generation processing is no longer required. Even if this strategy is not so widely adopted it’s worth considering when designing the database model.

When the database identifier generation responsibility falls to the database system, there are several strategies for auto incrementing surrogate keys:

Database engineAuto incrementing strategy
OracleSEQUENCE
MSSQLIDENTITY, SEQUENCE
PostgreSQLSEQUENCE, SERIAL TYPE
MySQLAUTO_INCREMENT
DB2IDENTITY, SEQUENCE
HSQLDBIDENTITY, SEQUENCE

Design aspects

Reference: Database primary key flavors from our JCG partner Vlad Mihalcea at the Vlad Mihalcea’s Blog blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

Leave a Reply


8 + three =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close