Updating Foreign Keys upon Primary Key changing in Sql Server

I’ve been doing a little (lot) of reading and wanted to try something I’ve never actually used in Sql Server. That is cascade updates.

The scenario is this!

  1. I have a table that has a primary key…. wow hey!
  2. I have another table that has a foreign key that references the primary key
  3. I want to update the primary key and have the foreign keys change their values

In this case the primary key is simply going to be a gender which will be a key that is char(10)

Please ignore my use of IDENTITY…  if you don’t like it… I’m beginning to understand you I think. I also didn’t really need it for the example. I added it early on, then changed a lot of things. I’m not going to remove it now.

/* This script shows how you can automatically update Foreign keys when you change a primary key. ********************************** IMPORTANT ********************************** I would create a test database to run this script on. It creates 3 tables and then deletes them. ******************************************************************************* */ /* Create tables */ -- Contains the list of keys that the Person and Contact table will -- be constrained to have in the Gender column... No other value can be put there CREATE TABLE GenderTypes ( Gender CHAR(10) PRIMARY KEY ) -- List of People. Has a Gender CREATE TABLE Person ( PersonID int PRIMARY KEY IDENTITY, Gender CHAR(10) FOREIGN KEY REFERENCES GenderTypes(Gender) ON UPDATE CASCADE ) -- List of Contacts. Has a Gender CREATE TABLE Contact ( ContactID int PRIMARY KEY IDENTITY, Gender CHAR(10) FOREIGN KEY REFERENCES GenderTypes(Gender) ON UPDATE CASCADE ) GO /* Insert some data into the tables. */ -- These are the catagories. Male, Female and Unknown (I know they are not -- really categories, but you will get the idea!) INSERT INTO GenderTypes(Gender) VALUES('Male') INSERT INTO GenderTypes(Gender) VALUES('Female') INSERT INTO GenderTypes(Gender) VALUES('Unknown') -- Create People with one of each Gender INSERT INTO Person(Gender) VALUES('Male') INSERT INTO Person(Gender) VALUES('Female') INSERT INTO Person(Gender) VALUES('Unknown') -- Create Contacts with one of each Gender INSERT INTO Contact(Gender) VALUES('Male') INSERT INTO Contact(Gender) VALUES('Female') INSERT INTO Contact(Gender) VALUES('Unknown') GO /* Change the "Unknown" Gender in the Categories table and watch the foreign keys change! */ -- Before the change SELECT s.PersonID, s.Gender AS SHGender, gt.Gender as Gender FROM Person s JOIN GenderTypes gt ON s.Gender = gt.Gender -- Change the Unknown Gender to UNKNOWN G UPDATE GenderTypes SET Gender = 'UNKNOWN G' WHERE Gender = 'Unknown' -- See if the Gender key has changed in both of the foreign keys that point to the -- GenderTypes tabels primary key SELECT s.PersonID, s.Gender AS SHGender, gt.Gender as Gender FROM Person s JOIN GenderTypes gt ON s.Gender = gt.Gender SELECT c.ContactID, c.Gender AS SHGender, gt.Gender as Gender FROM Contact c JOIN GenderTypes gt ON c.Gender = gt.Gender GO -- This just makes a bit of a division between the 6 results sets returned (well the -- (6 important ones!) SELECT '--------------- DIVIDER ----------------' GO /* Change the Gender back again and watch the foreign keys change! */ SELECT p.PersonID, p.Gender AS SHGender, gt.Gender as Gender FROM Person p JOIN GenderTypes gt ON p.Gender = gt.Gender -- Change the UNKNOWN G Gender back to Unknown UPDATE GenderTypes SET Gender = 'Unknown' WHERE Gender = 'UNKNOWN G' -- See if the Gender key has changed in both of the foreign keys that point to the -- GenderTypes tabels primary key. Should be back to Unknown. SELECT p.PersonID, p.Gender AS SHGender, gt.Gender as Gender FROM Person p JOIN GenderTypes gt ON p.Gender = gt.Gender SELECT c.ContactID, c.Gender AS SHGender, gt.Gender as Gender FROM Contact c JOIN GenderTypes gt ON c.Gender = gt.Gender GO -- Drop the tables! DROP TABLE Contact DROP TABLE Person DROP TABLE GenderTypes GO

The above is commented and I hope that they explain what is happening.

We’ve got the GenderTypes table which contains one column called Gender with a CHAR(10) data type. This column is the primary key.

We’ve got two tables called Contact and Person which basically have the same structure. In the declarations for the table I’ve added foreign key constrains to the GenderTypes tables Gender column. and also put the ON UPDATE CASCADE.

This will result in the following:

  1. The Person.Gender and Contact.Gender columns can only contain values that are in the GenderTypes table.
  2. When one of the rows in the GenderTypes is changed (That is the primary key is changed) the Person.Gender and Contact.Gender columns will be updated for every Foreign key that matched the primary key on the GenderTypes table before the update.
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: