Question :
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(@table_name)
+ ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';
ELSE
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ CASE @is_not_trusted
WHEN 0 THEN ' WITH CHECK '
ELSE ' WITH NOCHECK '
END
+ ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)
+ ' FOREIGN KEY (';
SET @tsql2 = '';
DECLARE ColumnCursor CURSOR FOR
select COL_NAME(fk.parent_object_id, fkc.parent_column_id)
, COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
on fk.object_id = fkc.constraint_object_id
where fkc.constraint_object_id = @constraint_object_id
order by fkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0;
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + QUOTENAME(@fkCol);
SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@referenced_schema_name) + '.' + QUOTENAME(@referenced_object_name)
+ ' (' + @tsql2 + ')';
SET @tsql = @tsql
+ ' ON UPDATE ' + CASE @update_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ ' ON DELETE ' + CASE @delete_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ CASE @is_not_for_replication
WHEN 1 THEN ' NOT FOR REPLICATION '
ELSE ''
END
+ ';';
END;
PRINT @tsql;
IF @action = 'CREATE'
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ CASE @is_disabled
WHEN 0 THEN ' CHECK '
ELSE ' NOCHECK '
END
+ 'CONSTRAINT ' + QUOTENAME(@constraint_name)
+ ';';
PRINT @tsql;
END;
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;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
To generate the DROP foreign keys script, modify @action value to be equal to ‘DROP’ in the declaration clause :
DECLARE @action char(6) = 'DROP';
Answer :
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:
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.
You sure can. ON UPDATE CASCADE is what you are looking for.
Here’s a small how-to:
http://sqlandme.com/2011/08/08/sql-server-how-to-cascade-updates-and-deletes-to-related-tables/
Basically, when you modify the PK, the cascade will go out and update all the FKs that reference it. This can be done in your CREATE statement, same as if you were doing a CASCADE DELETE
Keep an eye on things when you do this because, as I understand it, CASCADE actually runs at the isolation level SERIALIZABLE (normally, SQL runs at READ COMMITTED by default) behind the scenes so watch for any blocking issues.
Additional info on isolation levels can be found in this article: http://msdn.microsoft.com/en-us/library/ms173763.aspx
Define all the foreign keys as CASCADE UPDATE
If you have not done this, then you’ll have to
- Create a new row with new primary key
- Update all child tables
- Remove old row
.. in a transaction of course and watching out for other constraints that could fail