On Tue, 10 Jul 2001 10:26:35 -0400, David Ebert wrote:
>If the greater issue is changing a client ID number what other
strategies
>are there?
David,
The steps could be:
1) Insert new ID value in the primary key table, the rest of the row
looking just like the old one, except for the ID itself. You could do this
by projecting a temporary table with that one row, then updating the ID
in the temp table to the new value, then INSERT ... SELECT back into
the PK table from the temp table.
2) For each FK table, Update foreign key table, set FKvalue = new ID
value WHERE FKvalue = old ID value
3) Delete old ID value from the primary key table
Not a lot more complicated than all the steps you were thinking off with
the SET MULTI/ALTER TABLE/CASCADE stuff.
In general, good database design avoids the use of data elements that
might change as primary keys. For example, telephone number is not
a good primary key. An AUTONUM ID field is a better one.
Bill
