Changing a constituent ID number

I understand that there are a lot of foreign key constraints that make this difficult.

Let's say I have constituent A with constituent ID of 2 and I have constituent B with constituent ID of 20. I want to change constituent A to have the constituent ID of 20 and constituent B to have constituent ID of 2 (swapping their constituent ID numbers).

I tried dropping all constraints for all tables temporarily, allowing me to change the customer_no for constituent A's record from 2 to 20 and B's record from 20 to 2, but of course, the only information that moved was the name of the constituent, but all other data like ticket history, transactions, addresses, affiliations, etc were still there

I understand that there are a lot of foreign key constraints that make this difficult.

Let's say I have constituent A with constituent ID of 2 and I have constituent B with constituent ID of 20. I want to change constituent A to have the constituent ID of 20 and constituent B to have constituent ID of 2 (swapping their constituent ID numbers).

I tried dropping all constraints for all tables temporarily, allowing me to change the customer_no for constituent A's record from 2 to 20 and B's record from 20 to 2, but of course, the only information that moved was the name of the constituent, but all other data like ticket history, transactions, addresses, affiliations, etc were still there

Hi Henry

I think the first response would have to be: Why would you want to do that?

The constituent id is just a number, a non-semantic (ie meaning-less) primary key to identify the record - From a database theory point of view, it would be a somewhat unfortunate step to attach meaning to the number; and Tess is certainly built around that assumption.

And the second response is - You can't do that... or at least not safely, in a rational amount of time....

If you put a serious amount of work into identifying every place in the database where the customer_no was referenced, and then wrote a script to change them all around, in a sequence that avoids breaking any foreign key relationships, (you'd have to go via a third number along the way, logically), it might be sort of possible, but you'd always run the risk of missing something and breaking the system. 

If you really, really, really need to do this thing, I would suggest that manually editing each of the records might be simpler.

Or if you just want to move some constituents out of a particular range of id's, for some reason, you could create new constituent records for them, and merge the old records into the new ones. Actually, now that I think about that, you might be able to then merge the new records back into the old ones, the other way around, and with a bit of manual fixup, achieve what you want. But don't quote me on that. I certainly wouldn't do it myself.

Ken