Re: Primary Key of type string ?

On May 31, 7:41 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 31.05.2007 11:26, sybrandb wrote:
>
> > On May 31, 11:03 am, "Oriane" <ori..._at_guermantes.fr> wrote:
> >> Just a basic question from a beginner in Oracle. Is is the "standard choice"
> >> to use a column of type "int" for the primary key ? I suppose that this is a
> >> performance issue ? What about choosing a type "string/char" for a primary
> >> key ?
>
> > The standard choice is a number(10). Int is a non-existing datatype in
> > Oracle and will be automagically translated to number(39).
> > Numbers are stored packed, two digits in a byte.
> > Choosing string/char is a bad idea.
> > First of all, it will take more space, but secondly string comparisons
> > are internally more expensive: the string will be compared until the
> > first pair of characters that is different.
>
> And I thought the standard choice would be the key dictated by business
> requirements...
>
> > Do *NOT* store numbers padded with zeroes in a string!
> > This will result in much more I/O and all of your code is sensitive to
> > implicit type conversion, and Oracle will always convert the database
> > column to the variable, thus suppressing index usage.
>
> That's good advice.
>
> Kind regards
>
> robert

Oriane, you can find a complete description of the native Oracle data types in the Concepts manual. The manuals are available online at http://tahiti.oracle.com.

I vote with Robert. Wherever possible you should use business values as the PK. Only if the business value is subject to being changed would I consider using an artificial key.

HTH -- Mark D Powell -- Received on Thu May 31 2007 - 07:50:06 CDT