Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Using date as a primary key

## If I select the date as a primary key, what problems could I face in the long term?

If I select the date as a primary key, what problems could I face in the long term?

I don't see any, but before we discuss using date as a primary key, let's make a couple of assumptions about the...

dates that we're planning to store in our table.

First, let's assume that we're using the Gregorian calendar. As you may know, this calendar was adopted to replace the Julian calendar in most Western countries, but they did not all do it on the same day. The change has taken hundreds of years, between 1582 and the twentieth century, and during that time, what was May 13th for one person was May 24th for someone else. So the second assumption is that we're going to store dates in our database only from the current era. This is because a date in any of those earlier years is not enough to pin down exactly what day it was; more information would be needed, such as whether it is a Gregorian or Julian calendar date.

You see, that is the purpose of a primary key: to give identity to a real-world entity (thing, event, etc.). What we are identifying when we use a date is a particular day.

Let me give you a real example. Suppose we were interested in the day when we will next have a full moon on an equinox. We may not know what date that is, but it definitely is a single, particular day. It has identity. Even if we don't know what its date is, we do know that it exists (has identity in the real world, albeit in the future) because we know enough about celestial mechanics to be 100% positive that there will definitely be another full moon on an equinox, some day in the future.

With me so far?

Okay, now, the only two requirements for a primary key are:

1. it is never null
2. it uniquely identifies each row in the table

On this basis, a date makes an excellent primary key, provided we know which calendar we're using. And if we assume Gregorian, and we don't need to store historical dates, we're fine. Note that with date as the primary key, we could not enter the day of the next full moon on an equinox into our new table, until we actually compute the date, which cannot be null since it's the primary key.

Okay, you did ask whether there would be any problems in the long term. In the long term, we could theoretically change calendar systems. We could have thirteen months of twenty-eight days—accountants would love that setup—with one or two extra "intra-annual" days thrown in each year to make the total come out to 365 or 366 as necessary. Nothing stopping us. Well, except perhaps the worldwide investment in existing computer systems. You thought getting ready for Y2K was expensive? Imagine switching calendars! My personal bet is that we will never get off the Gregorian calendar, at least not before you and I and everyone we know is long, long gone, and intelligent computers that can reprogram themselves have been invented.

Of course, if you want to be safe, to ensure that your database survives even beyond that far off day, you could use a surrogate key. A surrogate key is an artificial key, like a distinct integer, which takes the place of the primary key. This way, the "real" key, the date in this instance, would be stored in a lookup table, and you would reference all rows to the lookup table using the surrogate key, to find out what the date is. Thus, you would store Fred's birthday not as 2937-12-23 but as something like 4529374.

Then, if the date ever has to change, like in a change from Gregorian to some new calendar system, e.g., from 2937-12-23 to 2937-13-21, then you would simply update the lookup table and none of the related tables would have to be updated because they are using the surrogate key instead! Fred's birthday would still be 4529374!

This (using a surrogate key) is exactly the advice many DBAs will give you. I should emphasize at this point that I am not a DBA, I am a data modeller, and in my opinion, using dates as a primary key is perfectly safe, at least as long as the original assumptions are true, that they are Gregorian dates and do not include ambiguous dates from the past. If those assumptions are true, using a surrogate key would be silly.

This was last published in November 2005

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Streaming tool from StreamSets eyes data in motion for GDPR

StreamSets software for inspecting big data brings governance to data in motion. Such capabilities may find more use as the ...

• ### Data expert: GDPR deadline is an opportunity, not a burden

There is stress as the EU's General Data Protection Regulation compliance deadline nears, but the GDPR privacy movement is a good...

In big data news, we find Google TPUs, or Tensor Processing Units, offered as a cloud service, while LinkedIn is open sourcing a ...

• ### Rethinking analytics processes spurs enterprise innovation

By taking a fresh look at the makeup of their analytics organizations, enterprises can innovate their business models and take ...

• ### Diversified data sets for analytics deliver top results

Analytics teams should focus on data diversity to ensure that their projects deliver the most meaningful insights -- but they ...

• ### How to boost the value of BI in today's analytics landscape

Traditional BI reporting still gives businesses valuable information. But its value can be increased by incorporating it into a ...

## SearchSAP

• ### SAP Ariba Live focuses on procurement for purpose

SAP Ariba Live 2018 focused on the idea that businesses can use procurement technology to do good in the world; for example, by ...

• ### SAP debuts consumption-based pricing model for SAP Cloud

SAP Cloud Platform is now available as a consumption-based model, an alternative to the subscription model. SAP also updated the ...

SAP paid \$2.4 billion to acquire lead-to-money vendor CallidusCloud, and analysts agree that the significant price may be worth ...

## SearchSQLServer

SQL Operations Studio simplifies routine administration of SQL Server and Azure SQL databases, making database development and ...

• ### Meltdown and Spectre fixes eyed for SQL Server performance issues

Microsoft has responded to the Spectre and Meltdown chip vulnerabilities with patches and other fixes. But IT teams need to sort ...

• ### Five SQL Server maintenance steps you should take -- ASAP

Putting off SQL Server administration tasks can lead to database problems. Enact these often-neglected maintenance items to help ...

## TheServerSide.com

• ### Why the Waterfall or Agile debate will be around forever

Which is the right methodology to use for your project: Waterfall or Agile? The industry may be at peak Agile, as the ...

• ### Chef's InSpec 2.0 brings compliance automation to the cloud

Enterprises have been quick to adopt automation tools for development and deployment but only recently have organizations started...

• ### Application security vulnerabilities are often known exploits

How hard is it to secure an enterprise application? It's not hard, especially given the fact that most application security ...

## SearchDataCenter

• ### IBM cloud services to secure mainframes out to the edge

Big Blue will introduce IBM cloud services that use blockchain, containers and its z14 mainframes to deliver improved security ...

• ### Four disadvantages of hyper-converged infrastructure systems

Problems with scalability and unexpected licensing costs can create problems for organizations that deploy hyper-converged ...

• ### IBM Power9 servers seek market inroads to AI, cloud

IBM follows up its first Power9 server with a raft of systems designed to appeal to a wider array of markets -- most notably, AI ...

## SearchContentManagement

• ### Scrivito unveils serverless CMS product

By building the CMS with ReactJS, Scrivito gained attraction with development community, according to an analyst.

• ### Content personalization tools sharpen focus on customers

Content personalization isn't new; Amazon weaponized it, and Jeff Bezos is the world's richest man. New tools are putting it ...

• ### Leading brands see the need for personalized content

Content personalization continues to expand within companies as maturing technologies make it a viable marketing option for ...

## SearchHRSoftware

• ### How people analytics can improve HR effectiveness

Getting insight into your workforce can reveal everything from training issues to the reasons for turnover or missed corporate ...

• ### At Ceridian, role of CIO requires constant learning, adjusting

You might say Warren Perlman, CIO at Ceridian, a global HCM software company, has been preparing for the role of CIO all his life...

• ### Human resources help desk multiplies HR staff efficiency

Here's how a college used a human resources help desk to better serve its 5,000-plus faculty and staff and enabled HR to focus on...

Close