Re: primary key and existing unique fields

On Tue, 26 Oct 2004 16:24:44 +0000, Sally Sally <dedeb17(at)hotmail(dot)com> wrote:
> Hi all,
> I am wandering about the pros and cons of creating a separate serial field
> for a primary key when I already have a single unique field. This existing
> unique field will have to be a character of fixed length (VARCHAR(12))
> because although it's a numeric value there will be leading zeroes. There
> are a couple more tables with similar unique fields and one of them would
> need to reference the others. Does anybody see any good reason for adding a
> separate autoincrement primary key field for each table? or either way is
> not a big deal.

I see three possible advantages:

1. having varchar(12) in every referencing table, takes more storage
space, which may mean something if you have tons of gigabytes of rows.
;)
2. if any of your varchar(12) row's data is likely to change in
future, you'll make update of one table, not an update which will
CASCADE over many tables.
3. If you'll need things like "last 50 keys", you can SELECT * FROM
foo ORDER BY yourserialkey DESC LIMIT 50;

And a disadvantage:
if you'll need to access the data by your varchar(12) key, you'll need
to perform JOIN on two tables. If you used varchar(12) as your key,
you don't. :)

Regards,
Dawid