All geek questions in one place

How to change primary key value and update foreign key at the same time

I have a table entry with an invalid primary key. I want to change it to the correct value, but this value is used in many other tables.

Is there an easy way to update the primary key and foreign key in the same tim?

+6
source share
3 answers

If cascading changes are set for foreign keys, the value should automatically change.

+8
source

Make sure your relationship with the foreign key is included in the UPDATE CASCADE, and the foreign key will be automatically updated according to the primary key.

From books on the Internet: http://msdn.microsoft.com/en-us/library/ms174123%28v=SQL.90%29.aspx

UPDATE {CASCADE | NO ACTION | ASK DEFAULT | SET NULL}

Determines what action happens to the row in the table that is created when this row has a referenced relationship and the referenced row is updated in the parent table. by default there is no ACTION. See the "Notes" section later in this section for more information.

+3
source

The primary key update does not update the related foreign keys, it only removes the related records in other tables, because Sql Server considers the update to be a delete and an insert. These are Sql Server 2000, not later versions. Using "for cascading updates for cascading deletes", the cascading effects "delete and paste: aka update" delete related records in other tables.

0
source

Source: https://habr.com/ru/post/890770/

More articles:


All Articles