Re: IS it a good practice to use SERIAL as Primary Key?

On Nov 28, 2006, at 9:55 , Joshua D. Drake wrote:

> On Tue, 2006-11-28 at 09:29 +0900, Michael Glaesemann wrote:
>> On Nov 28, 2006, at 9:02 , Joshua D. Drake wrote:
>>
>>> On Mon, 2006-11-27 at 17:31 -0600, John McCawley wrote:
>>>> I promise I'm not trying to be a pain in the butt ;) Do you
>>>> then use
>>>> your serial id as your foreign key in other tables, or the
>>>> firstname/lastname primary key?
>>>
>>> Now that is a good question. I would use the id, but that is not
>>> technically proper :).
>>
>> If you have both a surrogate key (the serial column) as well as a
>> natural key (e.g., the (first_name, last_name) composite key), what
>> difference does it make? You can get to the first_name, last_name
>> data via a join on the surrogate key.
>
> The point is easy data management...
>
> SELECT * FROM names;
> UPDATE names set first_name = 'foo' WHERE id = 6
> vs
> SELECT * FROM names;
> UPDATE names set first_name = 'foo' WHERE first_name = 'Joshua' AND
> last_name = 'Drake';
>
> Or did I misunderstand your question?

question was more rhetorical than anything else. In the case of
having both a surrogate key and a natural key, you have the advantage
of both. You can perform the update just as you describe on the names
table. Where I see an advantage of natural keys is when you're
updating a table that references "names". Then, all you've got is an
integer if you're using the surrogate key. UPDATE ... FROM and
DELETE ... USING are very helpful in this situation. Though I'd
rather build an app to perform such updates than enter them via
direct SQL.

Michael Glaesemann
grzm seespotcode net