Skip site navigation (1) Skip section navigation (2)

Re: pervasiveness of surrogate (also called synthetic) keys

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: Rob Sargent <robjsargent(at)gmail(dot)com>
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Date: 2011-05-03 03:07:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On 05/02/2011 10:06 PM, Rob Sargent wrote:
> You would be surprise how many "bob smith"s where born on the same 
> day.  But then they weren't all born in a hospital etc etc etc.

I wouldn't be surprised.  I once lived in a mile-square town (Hoboken, 
that's it's nickname).  In that town were 40K residents and three gyms.  
I forgot my ID card one day when going to mine, and they took my name 
and street name as alternate proof of identity.  Some designer along the 
line figured that was unique enough.  Number of Greg Smiths living on 
that street who were members of that one gym?  Three.

I see this whole area as being similar to SQL injection.  The same way 
that you just can't trust data input by the user to ever be secure, you 
can't trust inputs to your database will ever be unique in the way you 
expect them to be.  So if you build a so-called "natural key" based on 
them, expect that to break one day.  That doesn't mean you can't use 
them as a sort of foreign key indexing the data; it just means you can't 
make them the sole unique identifier for a particular entity, where that 
entity is a person, company, or part.

Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support
"PostgreSQL 9.0 High Performance":

In response to


pgsql-general by date

Next:From: John R PierceDate: 2011-05-03 03:25:38
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Previous:From: Craig RingerDate: 2011-05-03 02:52:23
Subject: Re: pervasiveness of surrogate (also called synthetic) keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group