Cascade primary key update to all referencing foreign keys

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ?

# EDIT 1:
When I run followinq query

select * from sys.foreign_keys where referenced_object_id=OBJECT_ID('myTable')

, I see that update_referential_action is set to 0. Thus NO ACTION is taken after updating my primary keys columns. How can I update the foreign keys to make them ON CASCADE UPDATE ?

# EDIT 2 :

In order to script out creation or dropping of all foreign keys in your schema run the following script (taken from here)

```
DECLARE @schema_name sysname;

DECLARE @table_name sysname;

DECLARE @constraint_name sysname;

DECLARE @constraint_object_id int;

DECLARE @referenced_object_name sysname;

DECLARE @is_disabled bit;

DECLARE @is_not_for_replication bit;

DECLARE @is_not_trusted bit;

DECLARE @delete_referential_action tinyint;

DECLARE @update_referential_action tinyint;

DECLARE @tsql nvarchar(4000);

DECLARE @tsql2 nvarchar(4000);

DECLARE @fkCol sysname;

DECLARE @pkCol sysname;

DECLARE @col1 bit;

DECLARE @action char(6);

DECLARE @referenced_schema_name sysname;

DECLARE FKcursor CURSOR FOR

select OBJECT_SCHEMA_NAME(parent_object_id)

, OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)

, object_id

, is_disabled, is_not_for_replication, is_not_trusted

, delete_referential_action, update_referential_action, OBJECT_SCHEMA_NAME(referenced_object_id)

from sys.foreign_keys

order by 1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

, @referenced_object_name, @constraint_object_id

, @is_disabled, @is_not_for_replication, @is_not_trusted

, @delete_referential_action, @update_referential_action, @referenced_schema_name;

WHILE @@FETCH_STATUS = 0

BEGIN

IF @action <> 'CREATE'

SET @tsql = 'ALTER TABLE '

+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@t

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint.

If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update.

EDIT: Since you do not have the ON UPDATE CASCADE constraint, but you want to have that set up, it is a bit of work. SQL Server does not support altering the constraints to a new setting.

It is necessary to iterate through each table that has a FK constraint to the PK table. For each table with the FK:

• ALTER TABLE to drop the existing FK constraint.

• ALTER TABLE again to create the ON UPDATE CASCADE constraint for the FK in question.

This takes a bit of effort, but would result in your constraint being properly set for your case.

EDIT 2: The information that you need is found in sys.foreign_keys. You can select from that table to get all the information you need.

A post from John Paul Cook can be found here:

(http://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx)

This code will drop and create ALL FK constraints in a database. You should be able to work from that to make only the changes that you want in your database.

StackExchange Database Administrators Q#84434, answer score: 14