RE: [LIP] which is the primary key

I think the idea is that the primary key should:

- discretely identify a row of data
- be an efficient index for lookup

The id field can more discretely identify a row of data than the name field (consider the case of 'banana' compared to 'Banana' vs. '1' compared to '2').

In a case such as this, it may be appropriate to describe the 'name' field as UNIQUE in the schema, so that although id is the primary key, 'name' will also be held as unique.

-----Original Message-----
From: linux-india-programmers-admin-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org [mailto:linux-india-programmers-admin-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org] On Behalf Of Kenneth Gonsalves
Sent: Wednesday, August 17, 2005 3:27 AM
To: linux-india-programmers-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
Subject: [LIP] which is the primary key

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?
 
-- 
regards
kg

http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!


-------------------------------------------------------
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
_______________________________________________
linux-india-programmers mailing list
linux-india-programmers-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/linux-india-programmers




-------------------------------------------------------
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