| Subject | Re: [firebird-support] Moving primary key to another column |
|---|---|
| Author | Tomasz Tyrakowski |
| Post date | 2012-08-06T13:33:46Z |
On 2012-08-05 19:26, Matthias Hanft wrote:
That's the procedure I've been using in the past in such cases:
1. drop the current primary key (alter table MyTable drop constraint
OldPrimaryKeyName)
2. create the generator (create generator MyGen)
3. create the new primary key column (alter table MyTable add NewKey not
null)
4. fill the new column using the generator:
update MyTable set NewKey=(select gen_id(MyGen,1) from RDB$DATABASE)
5. create the primary key anew (alter table MyTable add constraint
NewPrimaryKeyName primary key(NewKey) )
No extra table required.
regards
Tomasz
--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__
> Now I don't want to be NBR the primary key any more (the numbers thereMatthias,
> have a special meaning where I need to have some "0" entries now); I'd
> like to insert another "ID" colum which should be the new primary key
> (and filling it automatically from 1 to N by a generator).
That's the procedure I've been using in the past in such cases:
1. drop the current primary key (alter table MyTable drop constraint
OldPrimaryKeyName)
2. create the generator (create generator MyGen)
3. create the new primary key column (alter table MyTable add NewKey not
null)
4. fill the new column using the generator:
update MyTable set NewKey=(select gen_id(MyGen,1) from RDB$DATABASE)
5. create the primary key anew (alter table MyTable add constraint
NewPrimaryKeyName primary key(NewKey) )
No extra table required.
regards
Tomasz
--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__