Re:Modify SQL Key

Discussion created by ca.portal.admin on May 4, 2010
Is it possible to modify a key of an SQL table?
If so will it propagate down to other tables?
IDMS Public Discussion Forum


Re: Modify SQL Key

You can get your IDMS specific answer by trying it with a couple of test
tables but it should be possible. The answer you seek is likely in the
manual as well.

With that said, I would like to answer generically for all databases, SQL or
not. Just because it's possible doesn't mean it's a good idea to be
changing key data in any production database, even if cascading updates are
supported. The problem arises because the cascading update does not update
offline copies of the data, that is backups, files transferred in batch to
other in-house systems, files sent to business partners (EDI or SOA),
printed or electronic documents produced using the old key and then shared
with others, data warehouses, history files, etc. You get the point. It's
almost always a bad idea, but there are exceptions.

In general, if a column's value is expected to change after creation, it's
not a real key and you need to choose another field that will remain stable,
even if you have to manufacture one using a sequence number, GUID, etc. In
fact manufactured keys are often used as a matter of policy in SQL databases
because using the application's logical keys makes for really complicated
WHERE clauses, and much more complicated index requirements. It gets worse
the deeper your primary/foreign hierarchy gets.

No matter how you design your database, if the ""key"" data you share with
other systems is used in those systems as a key you are inviting a lot of
trouble down the line if you change the key values.


Tom Hebert
ObjEx, Inc.
(908) 813-2866