It worked great tnx

--- Helen Borrie <helebor@...> wrote:

> At 07:19 PM 1/11/2004 -0800, you wrote:
>
>
> >Hi,
> >
> >I want to add another column in my table and
> >it must be added as a primary key.
> >
> >The problem is, I have 2 columns that are currently
> >the primary keys of my table.
> >
> >Is this possible?
>
> Yes. Backup the database first. :-))
>
> Get exclusive access using gfix -shut and whatever
> level of forcing is
> required.
>
> Now, assuming you are going to use a generator to
> populate this new key:
> 1. alter table mytable add newpk integer not null;
> commit;
> 2. create generator newpk_gen;
> commit;
> 3. create procedure populate_pk
> as
> declare oldkey1 whatever;
> declare oldkey2 whatever;
> declare gen_val integer;
> begin
> for select old_key_1, old_key_2 from mytable
> where newpk is null --avoids recursion
> into :oldkey1, :oldkey2
> as cursor c do
> begin
> gen_val = gen_id(gen_pk, 1);
> update mytable set newpk = :gen_val
> where current of c;
> end
> end
>
> 4. Commit the SP; execute it and commit.
> 5. Drop any FK constraints, triggers, views, SPs
> that depend on the old
> primary key, commit
> 6. Drop the PK constraint on mytable, commit
> 7. Add the new pk constraint using the new column,
> commit.
>
> ./hb
>
>
>

__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com