A table of lookup codes has many tables which reference it via foreign-key
declarations, and I want to be able to merge two codes into one.
For example lets say we have a CUSTOMER table and it uses a lookup 'code'
field from the CUSTOMER_TYPE table.
create table customer (
...
typeCode text not null,
...
);
create table customerType (
code text not null,
...
constraint pk primary key (code)
);
alter table customer add constraint fk
foreign key (typeCode)
references customerType(code)
on update cascade;
Everything will work just fine if we change the value of a code in the
customerType table the values should propagate through to the customers.
But, if we have two codes in the code table, say 'wholesale' and
'distributor' and decide that the distributor type is no longer needed, we
need to set all customers (and about a dozen other tables) that were
distributor to wholesale. Although updating the entry of 'distributor' to
'wholesale' would update all the foreign references we can't since there is a
primary key on code in the customerType table.
The best way I can think of doing this is to write a procedure which will
lock the old code in the code table, search the data dictionary for all
dependents, loop through each of them and construct/execute dynamic update
queries to alter the appropriate dependent records, and then deletes the old
code from the code table (this is how we did it in oracle).
Anyone have a better approach?
Assuming this is the right way to do this can someone advise me where/how to
extract the required data from the pg_xxx tables?
thanks,
Mike.
===================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com
