How to update all foreign keys in a database

Question

Is there a way to update one column and all foreign keys that refer to it in a database? I have a table with an id column that I would like to update with the following query:

UPDATE Project SET id = id + 15;

but I get the following error:

Msg 8102, Level 16, State 1, Line 33 Cannot update identity column 'id'.

There are a lot of tables that refer to this column, and I'm unsure which ones have an Update cascade and which ones do not.

Why do you want to update your PK? Its usually a design flaw if you do... it seems like your PK is *meaningful* which is shouldn't be. If you need a meaningful id add a separate column. If you really really do need to update your PK, its a real complex problem, involves dropping FKs and PK, temp columns, updating data, renaming columns, reinstating PK + FK... with plenty of scope to get it wrong. — Dale K, Aug 11 '22 at 22:25
This screams of being an [xy problem](https://xyproblem.info/). What you are trying to do is really horrific to do. Can it be done? Yes. Is it a good idea? Very unlikely. Is it extremely involved and takes a ton of effort and time? Yes. Instead, let's try to understand what you are trying to solve and help you find a way to solve it. — Sean Lange, Aug 11 '22 at 22:28

score 0 · Answer 1 · answered Aug 12 '22 at 07:23

Though there is no straightforward solution but it can be done in the following steps:

First, update child tables: You must retrieve all tables that reference your Project table and make a query to disable relation constraint check on them and then update the FK columns on each table separately. The following query is based on this post that allows to make two commands: cmd1 for disabling relations and updating the FK columns and cmd2 to restore relation checks.

SELECT
    fk.name 'FK Name',
    tp.name 'FK table',
    cp.name 'FK column',
    'ALTER TABLE ' + tp.name + ' NOCHECK CONSTRAINT ' + fk.name + ';' + 'update ' + tp.name + ' set ' + cp.name + ' = ' + cp.name + ' + 15;' cmd1,
    'ALTER TABLE ' + tp.name + ' CHECK CONSTRAINT ' + fk.name cmd2
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id and tr.name = 'Project'
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id

you can now copy and paste the cmd1 column to update all child tables.

Second, update the parent table: Your PK is identity as the error reads. As you know updating an identity column is not possible but there is a workaround:

--Copy the date to a new table without an identity column
select * into Project_backup from Project union all select * from Project where 1<>1

--Update PK
update Project_backup  set id = id + 15

--Delete old data
delete from Project

--Enable identity insertion
SET IDENTITY_INSERT Project ON

--Insert into table
insert into Project (id,... /*List all columns here*/)
select * from Project_backup

--Disable identity insertion again
SET IDENTITY_INSERT Project off

Finally, enable relations constraints: Using the cmd2 column of the above query now you can restore the relations.

Finally It’s done!

How to update all foreign keys in a database

1 Answers1