Creating a primary key when your table has no unique records

By: Quinn Wildman

Abstract: Describes a simple technique to add a key regardless of the format of your data

If you do things right, you create a primary key for all your tables when you first create them and devise a scheme to insure the key is unique. However, what happens if you you don't do things right and decide to add a key after the fact? Here's a simple script you can run in isql which will add a key named OBJID to the table named mytable and make it be your primary key regardless of the format of the table.

alter table mytable add objid int not null;
create generator gen1;
set term ^;
create trigger tempupdatemytable for mytable before update
as begin
new.objid = gen_id(gen1,1);
end^
create trigger setkeyformytable for mytable before insert
as begin
new.objid = gen_id(gen1,1);
end^
set term ;^
update mytable set objid=1;
commit;
drop trigger tempupdatelicenses;
alter table mytable add primary key (objid);

Server Response from: ETNASC04