While conceptually true, you would introduce an application problem if
you make the phone number a PK. In Oracle, you cannot modify the value
of a PK. If someone changes their phone number, the app would be forced
to delete the old and insert the new ... which might cause other
problems with referential integrity. PKs are better left with absolutely
no intelligence built in ... kinda like me. :) Whenever someone thinks
about using smart numbers as keys in your database, it's time to start
asking a few pointed questions.
Kevin Gillins wrote:
> wouldn't a sequence actually generate a problem of duplicates when not
> wanted. Say the PK is phone number. Then, using a sequence as the PK would
> require a UNIQUE index on the phone number causing more overhead. If you
> just begin with a solid design of PK/FK relationships then you can let the
> database do the work for you.
>
> Kevin
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:umDk9.40468$g9.116855_at_newsfeeds.bigpond.com...
>
>>Hi Christopher,
>>
>>I'll go for option two. Avoids having to unnecessarily perform the select
>>and access the index twice.
>>
>>I would also recommend the use of a sequence (if appropriate) to eliminate
>>the potential duplicate key error.
>>
>>Cheers
>>
>>Richard
>>"Christoph Seidel" <chris666.seidel_at_gmx.de> wrote in message
>>news:amut5g$9edrd$1_at_ID-143718.news.dfncis.de...
>>
>>>what is better in respect of performance?
>>>
>>>select by primary key, if not found insert
>>>
>>>or
>>>
>>>insert and catch exception if primary key exists
>>>
>>>the table is quite small, less than 100 rows
>>>
Received on Thu Sep 26 2002 - 10:55:02 CDT
