Re: [LIP] which is the primary key

On 17/08/05 15:56 +0530, Kenneth Gonsalves wrote:
> hi,
> i have had occasion to interact with two rdbms gurus over the past 
> week and they have challenged one of my most precious assumptions. 
> Say we have two tables like so:
> 
> create table states
> 	(id	serial unique,
>         name varchar(50)
> 	);
> and
> create table towns
> 	(
> 	id serial unique,
> 	name varchar(50)
> 	state integer references states(id)
> 	);
> 
> i put the name field as the primary key in each case, but they argue 
> that the id key is to be the primary key. I always thought that the 
> primary key was the one that uniquely defined a row. Of course, 
> making the 'id' field the primary key would also uniquely define the 
> row, but then the possibility arises of having rows that *only* 
> differ in the 'id' field which doesnt sound like a good practice to 
> me. This becomes more apparent when considering the case where the 
> primary key has more than one field. 
> 
> comments anyone?

A primary key is:
Unique
Not NULL
The Default target for a foreign key relationship.

Ideally, the primary key should not depend on any data. Consider the
following example:

CREATE TABLE towns (
	name	varchar		primary key,
	state	varchar		not null
);

INSERT INTO towns (name, state) values ('Bombay', 'Maharashtra');

Now, if name is the primary key, then a table address referencing towns will
have address.town = 'Bombay'. If the city is now renamed to 'Mumbai', all
places where 'Bombay' is referenced will have to be updated to 'Mumbai'.

With DDL like this:

CREATE TABLE towns (
	id	integer		primary key,
	name	varchar		not null,
	state	varchar		not null
)
CONSTRAINT uniq_town (name, state) unique;

the above becomes possible without affecting any other table.

Requiring that columns other than the id be unique is not an issue. The
problem is with having a data field be the primary key.

Devdas Bhagat


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf