:::: MENU ::::

How to update associated foreign keys when primary key specification is changed

  • Apr 27 / 2009
  • 2
Constraints and Keys, DBA Interview questions, dbDigger, T-SQL Interview Questions

How to update associated foreign keys when primary key specification is changed

I had to change a auto generated primary key with some meaningful key value. After designing the meaningful key it was now time to consider the impacts on foreign keys related to that specific primary key. I planned to first script all related keys for re creation after the key change operation.
In test environment i just changed the primary key through SSMS to see the impact and surprisingly SSMS asked my permission to change the related foreign keys also. I was glad and task was successfully completed with some clicks only.
To go on with an example let us choose some keys in AdventureWorks
Check primary key to be changed

USE AdventureWorks
GO

— Check Specifications of primary key to be changed
sp_help [HumanResources.Department]
GO

Specification of Primary key to be changed

We can confirm that a primary key [DepartmentID] exists in the table. Data type of [DepartmentID] is smallint. Also we may confirm that this primary key is referenced as a foreign key in table [HumanResources.EmployeeDepartmentHistory].
Now i have to update the primary key [DepartmentID] in HumanResources.Department. For this purpose

  • right click on table
  • go to design
  • In design window change data type of [DepartmentID] from smallInt to Int
  • Just as you click out of modified data type cell, you will be facing following confirmation dialog

Confirmation about data type change

Dialog wants your permission to change data types of associated foreign keys. If you click yes data types of associated foreign keys will be changed and relations will be preserved with new data type. So just save the design changes and get your changes implemented. If you click no data type of primary key will not be changed.
Now confirm the foreign key data type change

USE AdventureWorks
GO

— Check data type change in foreign key
sp_help [HumanResources.EmployeeDepartmentHistory]

In the result we may confirm the change in data type of foreign key in [HumanResources.EmployeeDepartmentHistory].
So you do not have to plan any stretigies for synchronnizing the changes made in any foreign key. SQL Server Management Studio (SSMS) designer will work for you.

  • Anonymous

    It is very nice to know this feature. I was also planning to carry on such task. And i am glad to know that automatic update is provided by SQL Server itself.

  • Atif Shehzad

    By such examples we may make a rule that go for rough implementation on test server to view the problems and impacts on plannings.

Consult us to explore the Databases. Contact us