Re: select + insert vs. insert and catching exception

"Karsten Farell" <kfarrell_at_medimpact.com> wrote in message news:q5Gk9.3137$lj7.78759108_at_newssvr21.news.prodigy.com...
> 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.

As written, that statement is off the scale of incorrectness. Of course you can modify a primary key. I do it all the time... as does any update to any column executed through an Oracle Forms app, usually.

Perhaps you were thinking of foreign keys? Even then the statement is only half-right: the primary key of the child table in a parent-child relationship is perfectly updateable.

Of course, updating the primary key of a parent table is more problematic, because of the dependent child records. But even there, it's possible to do a 'cascaded update' by judicious use of triggers (or, rather more drastically, a simple disabling of the foreign key constraint).

There is nothing intrinsically special about a primary key that prevents it being updateable.

Regards
HJR
>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 - 15:19:07 CDT