Re: obtain primary keys

Sybrand Bakker wrote:
> On 10 Feb 2005 14:00:27 -0800, phph109_at_yahoo.es wrote:
>
> >Hello all!
> >
> >First, I'm not a dba- I am a sysadmin. company is "between" dba's so
I
> >have to take care of an oracle database used for developping a
webapp.
> >
> >The application uses stored procedures where I see a lot of:
> >
> > lock table t in exclusive mode nowait;
> > select max(ix) + 1 into pix from t;
> > insert into t values (pix, etc...);
> > commit;
> >
> >To me this locking seems a bit drastic.
> >
> >One way of doing it would be using sequences from triggers, like:
> >
> > create or replace trigger tinsert
> > before insert on t for each row
> > begin
> > if :new.ix is not null then
> > raise_application_error(-20000, 'ix must be null');
> > else
> > select seq_t_ix.nextval into :new.ix from dual;
> > end if;
> > end;
> >
> >What I like with this is that there is no way the programmers can
fail
> >:-) but there are a slight drawback when it comes to concatenated
> >primary keys.
> >
> >There are parts of concat keys that needs to be changed from
number(6)
> >to number(13) if to use sequences. This might be a stupid question,
but
> >it is somehow possible to estimate, calculate or test how such
change
> >will effect performance and disk usage?
> >
> >Any thoughts on this? Is there an "Oracle-way" or "best practice" on
> >how to get primery key values?
> >
> >Them programmers are used to sql-server and claims that there is a
> >special datatype for this there but this does not seem to be the
case
> >for Oracle- or am I wrong?
> >
> >It's on 10.1.0.3 if that matters.
> >
> >Thanks for your time,
> >ph
>
> 1 Concatenated primary keys are best avoided at all
> 2 For non-concatenated primary keys usually the number(10) definition
> is more than sufficient.
> 3 There is no special datatype and there is no need for it as there
> are sequences. The advantage of a sequence is, it is not bound to a
> table.
> I don't think you will even notice changing a single key column from
> number(6) to number(13). However, you'll probably will notice the use
> of a composite primary key.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Let us not forget foreign keys referencing the currently defined primary key as they are likely to be invalidated by the change. One needs to find all foreign key constraints and change those column definitions as well.

David Fitzjarrell Received on Thu Feb 10 2005 - 19:47:56 CST