How to Update the primary key of table which is referenced as foreign key in another table?

Question

Suppose a

Table "Person" having 
    "SSN",
    "Name",
    "Address"

and another

Table "Contacts" having
    "Contact_ID",
    "Contact_Type",
    "SSN" (primary key of Person)

similarly

Table "Records" having
    "Record_ID",
    "Record_Type",
    "SSN" (primary key of Person)

Now i want that when i change or update SSN in person table that accordingly changes in other 2 tables.

1. If anyone can help me with a trigger for that
2. Or how to pass foreign key constraints for tables

Answer 1

Just add ON UPDATE CASCADE to the foreign key constraint.

Answer 2

Well, assuming the SSN is the primary key of the Person table, I would just (in a transaction of course):

• create a brand new row with the new SSN, copying all other details from the old row.
• update the columns in the other tables to point to the new row.
• delete the old row.

Now this is actually a good example of why you shouldn't use real data as table cross-references, if that data can change. If you'd used an artificial column to tie them together (and only stored the SSN in one place), you wouldn't have the problem.

Answer 3

Preferably the primary key of a table should never change. If you expect the SSN to change you should use a different primary key and have the SSN as a normal data column in the person table. If it's already too late to make this change, you can add ON UPDATE CASCADE to the foreign key constraint.

Answer 4

If you have PKs that change, you need to look at the table design, use an surrogate PK, like an identity.

In your question you have a Person table, which could be a FK to many many tables. In that case a ON UPDATE CASCADE could have some serious problems. The database I'm working on has well over 300 references (FK) to our equivalent table, we track all the various work that a person does in each different table. If I insert a row into our Person table and then try to delete it back out again (it will not be used in any other tables, it is new) the delete will fail with a Msg 8621, Level 17, State 2, Line 1 The query processor ran out of stack space during query optimization. Please simplify the query. As a result I can't imagine an ON UPDATE CASCADE would work either when you get many FKs on your PK.

I would never make sensitive data like a SSN a PK. Health care companies used to do this and had a painful switch because of privacy. I hope you don't have a web app and have a GET or POST variable called SSN with the actual value in it!! Or display the SSN on every report, or will you shred all old printed reports and limit access to who views each report., etc.

Answer 5

Cascade update and delete are very dangerous to use. If you have a million child records, you could end up with a serious locking problem. You should code the updates and deletes instead.

You should never use a PK with the potential to change if it can be avoided. Nor should you ever use SSN as a PK because it should never be stored unencrypted in your database. Never, unless your company likes to be sued when they are the cause of an indentity theft incident. This is not a design flaw to shrug off as this is legacy, we don't have time to fix. This is a design flaw that could bankrupt your company if someone steals your backup tapes or gets the ssns out of the sytem in another manner (most of these types of thefts are internal BTW). This is an urgent - must fix now design flaw.

SSN is also a bad candidate because it changes (people change them when they are victims of identity theft for instance.) Plus an integer PK will have faster performance than a nine-digit PK.