Celko On SQL: Identifiers and the Properties of Relational Keys - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

IoT
IoT
Software // Information Management
News
8/19/2007
10:07 PM
50%
50%

Celko On SQL: Identifiers and the Properties of Relational Keys

He's back! Author, SQL expert, puzzlemaster and Ming the Merciless look-alike Joe Celko offers his sage (if offbeat) advice on unique identifiers, validation, verification, trusted sources and relational keys.

Joe Celko
Joe Celko
It's good to be back writing for Intelligent Enterprise after a long break. I was writing for Intelligent Enterprise before it was Intelligent Enterprise. Let me explain. In the early '90s, I had a column ("Celko on SQL") in a magazine named Database Programming & Design. I then moved my column over to DBMS magazine, a competitor, in 1993. Both magazines were later combined into Intelligent Enterprise, which soon after joined the CMP portfolio along with a bunch of other Miller-Freeman publications. Did I mention that I had columns in CMP magazines decades before all of this?

In spite of all the changes, I'm still me. Aristotle's Law of Identity guaranteed that I would not become J. K. Rowling. Darn! This leads to the questions, what is an identifier, why is a thing itself and what are desirable properties of relational keys when we want to model things in a database? (Celko fans, check out Joe's "Can't Be Your Own Supervisor" SQL Puzzle blog and post your own answer.)

Uniqueness is Global, Not Local

Everyone agrees that an identifier should be unique. But new database programmers often think that being locally unique is good enough. This leads them to cheerfully use a proprietary auto-increment or IDENTITY column as a PRIMARY KEY instead of as a proper relational key. Aristotle's Law of Identity is usually stated as "A is A," but there was a lot more to it than a formula. Every entity has a nature and follows its nature; every entity is separate and unique from all others in the universe.

Auto-numbering to build a key is simply dead wrong. It's a local value created in the physical hardware that has nothing to do with the global nature of the entity. If you don't like Aristotle, then consider this geek joke:

"I don't know my mother's phone number, but it's "1" on my speed dial."

"If you lose your phone, how can you call her?"

"I can borrow someone else's phone, dial "1" and get her."

"That's not going to work!"

"Sure it will! I set the Operator to zero and tested it!"

A Natural Key can be inherent characteristics, such as DNA signatures, fingerprints and longitude/latitude pairs. You need a scale and measurement system so you can test them with an instrument to confirm them. I don't know the rules for DNA matching, but in the U.S., fingerprints are classified in the Henry-Galt system (other countries have different systems) and I can get my location with a GPS built into a wrist watch.

National and International Standards are also Natural Keys. For example, the ISO 3779 Vehicle Identification Number (VIN) can be verified by lifting the hood of the car and reading the 17 characters from the engine block. I can use the UPC and EAN barcodes on retail products for ordering and inventory anywhere on Earth.

What makes them Natural Keys is that they can be verified and validated in the real world, and I have a trusted source for the encodings. Let's talk about those traits.

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
Previous
1 of 3
Next
Comment  | 
Print  | 
More Insights
InformationWeek Is Getting an Upgrade!

Find out more about our plans to improve the look, functionality, and performance of the InformationWeek site in the coming months.

News
Becoming a Self-Taught Cybersecurity Pro
Jessica Davis, Senior Editor, Enterprise Apps,  6/9/2021
News
Ancestry's DevOps Strategy to Control Its CI/CD Pipeline
Joao-Pierre S. Ruth, Senior Writer,  6/4/2021
Slideshows
IT Leadership: 10 Ways to Unleash Enterprise Innovation
Lisa Morgan, Freelance Writer,  6/8/2021
White Papers
Register for InformationWeek Newsletters
Video
Current Issue
Planning Your Digital Transformation Roadmap
Download this report to learn about the latest technologies and best practices or ensuring a successful transition from outdated business transformation tactics.
Slideshows
Flash Poll