Subject: Re: surrogate key or not?
From: Rod Taylor <pg@rbt.ca>
To: Josh Berkus <josh@agliodbs.com>
Cc: Iain <iain@mst.co.jp>, Markus Bertheau <twanger@bluetwanger.de>,
	pgsql-sql@postgresql.org
In-Reply-To: <200407210001.00102.josh@agliodbs.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<004101c46ec2$631ca930$7201a8c0@mst1x5r347kymb>
	<200407210001.00102.josh@agliodbs.com>
Content-Type: text/plain
Message-Id: <1090422650.21450.13.camel@jester>
Mime-Version: 1.0
Date: Wed, 21 Jul 2004 11:10:51 -0400
Content-Transfer-Encoding: 7bit

> All three of these implementation issues are, at least in theory, 
> surmountable.   For example, Sybase overcame problems (1) and (3) by creating 
> an automated, system-controlled hash key based on the table's real key.  This 
> was a solution endorsed by E.F. Codd in the mid-90's when he came to regret 
> his promotion of the "Primary Key" idea in the SQL standard.  

I've often wondered if the OID was intended to be something similar. 

You specify:

	CREATE TABLE abc (key varchar(120) PRIMARY KEY);
	CREATE TABLE bcd (key varchar(120) REFERENCES abc);

PostgreSQL was actually supposed to create:

	abc(oid oid, key varchar(120) PRIMARY KEY);
	bcd(key oid REFERENCES abc(oid));

Then muck around with the queries to resolve the actual varchar() name
when required.