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.

## SearchDataManagement

• ### Oracle brings GoldenGate data integration service to cloud

Oracle is making its GoldenGate real time data technology available on its second-generation Oracle Cloud Infrastructure platform...

• ### Era Software raises \$15.25M for enterprise data management

The startup that began as the EraDB time series database is advancing its efforts with new funding and a cloud service for its ...

• ### Dgraph GraphQL database users detail graph use cases

Graph DB vendor Dgraph Labs is expanding its AWS cloud footprint with new regions and adding change data capture capabilities in ...

• ### TigerGraph unveils support for GCP, adds new connectors

Graph database vendor TigerGraph unveiled support for Google Cloud and new connectors to Snowflake and Tableau on April 21 during...

• ### Startup Veezoo emerges from stealth with NLQ-based platform

Aiming to be 'Siri for enterprises,' an analytics startup emerged from stealth with a platform that enables users to interact ...

• ### 15 data science tools to consider using in 2021

Numerous tools are available for data science applications. Read about 15, including their features, capabilities and uses, to ...

## SearchSAP

• ### S/4HANA Cloud SaaS ERP: Buying team overview

SAP's multi-tenant SaaS ERP, S/4HANA Cloud, is a viable choice for companies that need ease in their infrastructure management. ...

• ### SAP forms financial services partnership with Dediq

SAP and financial industry investment firm Dediq are forming a new business unit to develop applications that help banks and ...

• ### Unpatched applications threaten SAP security

Cyberattacks are a significant threat to unpatched, unprotected SAP applications, according to a new threat intelligence report ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Incorporate diversity and inclusion in technology design

DEI in technology is about more than creating a diverse workplace. We talked to a few DEI professionals about how teams build ...

• ### Microsoft previews OpenJDK distro to the delight of devs

In a move meant to attract more Java developers to its Azure cloud and further support the Java community, Microsoft launched a ...

• ### Supreme Court ruling on Java APIs eases developer worries

Now that the Supreme Court has ruled for Google over Oracle in their high-stakes copyright battle over Java APIs, developers can ...

## SearchDataCenter

• ### Nvidia SDK simulates quantum computing circuits on GPU systems

Nvidia edged its way into the quantum computing market with an SDK that simulates quantum circuits by adding horsepower to ...

• ### Programmable processor technology for next-gen data centers

The right processing technology can benefit your data center. Learn about advancements in CPU technologies, recent vendor ...

• ### Data processing units accelerate infrastructure performance

DPUs often run on networking packets to move information in the data center, instead of supporting processing workflows. Get an ...

## SearchContentManagement

• ### OpenText releases Cloud Editions content services updates

OpenText CE 21.2 includes federated document compliance that extends to Microsoft Office 365, along with a revamped content ...

As the pandemic disrupts paper workflows, Adobe courts small business users with simple webforms, digital signatures and payments...

• ### Know when to use a headless CMS and when it's not worth it

Headless CMSes aren't a silver bullet for every circumstance. Evaluate three solid use cases for a headless CMS and three ...

## SearchHRSoftware

• ### 10 steps to support your HCM system post go-live

To ensure a new system's success, HR leaders need to develop a plan for how they will support their new HCM system post go-live. ...

• ### Face mask detection a newcomer to employee surveillance

Face mask detection has emerged as another form of employee surveillance technology, and its adoption may be helped indirectly by...

• ### 20 diversity and inclusion tools to power your DEI program

Diversity and inclusion tools shouldn't replace strategy, but these tools can help companies in multiple ways, from tracking ...

Close