Subject Re: [firebird-support] altering primary keys
Author Helen Borrie
At 07:19 PM 1/11/2004 -0800, you wrote:

>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

Now, assuming you are going to use a generator to populate this new key:
1. alter table mytable add newpk integer not null;
2. create generator newpk_gen;
3. create procedure populate_pk
declare oldkey1 whatever;
declare oldkey2 whatever;
declare gen_val integer;
for select old_key_1, old_key_2 from mytable
where newpk is null --avoids recursion
into :oldkey1, :oldkey2
as cursor c do
gen_val = gen_id(gen_pk, 1);
update mytable set newpk = :gen_val
where current of c;

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.