Add to primary key without dropping foreign key references

Question

I have a table with a primary key into which I would like to add another column. The problem is that when I add this, Sql Server drops all foreign key references to this table.

Is there any way to fix this? (or maybe even a tool that reorganizes this)?

Answer 1

There is nothing to “fix” - you change PK, so FK needs to be dropped.

What do you think should happen if you add a field to PK - will all FK links also add a field? What if this additional field does not exist in other tables? They need to be discarded and then recreated to make sure that they are even reliable links.

Answer 2

There is no way around this. Foreign keys must be redefined. If, for example, you add a column to your PC, all existing FKs must be redefined to refer to the new set of columns that make up PK. This means you have to add columns to child tables.

I am not sure if there are any tools that automate this process.