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

__________________________________________________ _______________
Don’t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/o...ave/direct/01/


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Since you already have the unique field I see no point in adding a sequence
to the table, unless of course the sequence of the data inserts is of
importance at some point.

Duane

-----Original Message-----
From: Sally Sally [mailto:dedeb17 (AT) hotmail (DOT) com]
Sent: Tuesday, October 26, 2004 9:25 AM
To: pgsql-general (AT) postgresql (DOT) org
Subject: [GENERAL] primary key and existing unique fields

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

__________________________________________________ _______________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/o...ave/direct/01/


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sally Sally wrote:
Your primary key should not be directly related to the data being
stored. Outside of the fact that it is the primary reference or the row.

Sincerely,

Joshua D. Drake




--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd (AT) commandprompt (DOT) com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Can you please elaborate on the point you just made as to why the primary
key should not relate to the data (even for a case when there is an existing
unique field that can be used to identify the record)

__________________________________________________ _______________
Check out Election 2004 for up-to-date election news, plus voter tools and
more! http://special.msn.com/msn/election2004.armx


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Sally Sally wrote:
Here is a good article on the topic:

http://www.devx.com/ibm/Article/20702


Sincerely,

Joshua D. Drake



--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd (AT) commandprompt (DOT) com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Joshua D. Drake wrote:
That article makes me want to vomit uncontrollably! ;-)

"Business data might also simply be bad -- glitches in the Social
Security Administration's system may lead to different persons having
the same Social Security Number. A surrogate key helps to isolate the
system from such problems."

The surrogate key isn't solving the underlying logical inconsistency
problem. It is being used as a work-around to cover one up. I suspect
the author of being a MySQL user.

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Actually he is a software project consultant for IEEE.org and he holds
a Ph.D. in Theoretical Physics.

Sincerely,

Joshua D. Drake



--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd (AT) commandprompt (DOT) com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Joshua D. Drake wrote:
<joking>

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

</joking>

Seriously, my only point was that Date & Darwen and other relational
purists do not use surrogate keys. The surrogate-key vs. speaking-key
debate devolves quickly. I'd only argue that it is wrong as the author
of the article implied that the speaking-key side of the debate is
without merit.

He begins:

"For the purpose of data modeling, the plumbing should be largely
transparent. In fact, purist DB lore makes no distinction between data
and plumbing. However, you will see that it is more efficient for
administration and maintenance, as well as in terms of runtime
performance, to have some additional fields to serve as DB keys."

So he dismisses the speaking-key argument in one sentence as "purist DB
lore." He then proceeds with a poor example:

"The requirements for a primary key are very strict. It must:

Exist
Be unique
Not change over time
Surrogate keys help to mitigate the fact that real business data never
reliably fulfills these requirements. Not every person has a Social
Security Number (think of those outside the U.S.), people change their
names, and other important information."

1. The reason we have ON UPDATE CASCADE is to handle changes in primary
keys.

2. If not everyone has a social security number than the design should
be sufficiently normalized to reflect that fact.

I am not saying that Chris Date and Hugh Darwen are right and that
Philipp Janert is wrong. I am only saying that both sides should be
investigated and judged on the weight of their arguments.

Personally, I've found over time that when I deviate from "purist DB
lore" I get punished in long run.

Could be wrong, though. :-)

Mike Mascari








---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Tue, 26 Oct 2004 16:24:44 +0000, Sally Sally <dedeb17 (AT) hotmail (DOT) com> wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Look at the database design in terms of data retrieval. If I add a sequence
number as my primary key, when I get ready to retrieve that record
"directly" how do I know what that sequence number is. For instance, my
employee number is 123456789, and it is unique within my company and my
sequence number is 375. I will more likely know the employee number to
query than I will the sequence number. Sure saves time in data access. I
know, you can always create a unique index on the employee number as well as
a primary index on the sequence number but WHY would I want to take up room
for a field in the record as well as the useless index space for no purpose.

Codd said "the key, the whole key and nothing but the key." In database
design we cannot always do this for query performance but why add something
to a record that will serve no real purpose. Keys are updateable, hopefully
they are not changed all that often but the ability should be there. If
they are not updateable then the database engine is not one I would choose
for my application.

Duane
-----Original Message-----
From: Mike Mascari [mailto:mascarm (AT) mascari (DOT) com]
Sent: Tuesday, October 26, 2004 2:26 PM
To: Joshua D. Drake
Cc: Sally Sally; pgsql-general (AT) postgresql (DOT) org
Subject: Re: [GENERAL] primary key and existing unique fields

Joshua D. Drake wrote:
<joking>

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

</joking>

Seriously, my only point was that Date & Darwen and other relational
purists do not use surrogate keys. The surrogate-key vs. speaking-key
debate devolves quickly. I'd only argue that it is wrong as the author
of the article implied that the speaking-key side of the debate is
without merit.

He begins:

"For the purpose of data modeling, the plumbing should be largely
transparent. In fact, purist DB lore makes no distinction between data
and plumbing. However, you will see that it is more efficient for
administration and maintenance, as well as in terms of runtime
performance, to have some additional fields to serve as DB keys."

So he dismisses the speaking-key argument in one sentence as "purist DB
lore." He then proceeds with a poor example:

"The requirements for a primary key are very strict. It must:

Exist
Be unique
Not change over time
Surrogate keys help to mitigate the fact that real business data never
reliably fulfills these requirements. Not every person has a Social
Security Number (think of those outside the U.S.), people change their
names, and other important information."

1. The reason we have ON UPDATE CASCADE is to handle changes in primary
keys.

2. If not everyone has a social security number than the design should
be sufficiently normalized to reflect that fact.

I am not saying that Chris Date and Hugh Darwen are right and that
Philipp Janert is wrong. I am only saying that both sides should be
investigated and judged on the weight of their arguments.

Personally, I've found over time that when I deviate from "purist DB
lore" I get punished in long run.

Could be wrong, though. :-)

Mike Mascari








---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly