如何更新主键 [英] How to update primary key

问题描述

这里是我的问题：
我有两个表：

1. WORKER，列 ID | OTHER_STAF | ，其中ID是主键


2. FIRM，与列 | FPK | ID | SOMETHING_ELSE | ，其中组合FPK和ID构成主键，ID也是引用到WORKER.ID的外键（非空，且必须与WORKER中的值相同）。
   

我想创建存储过程UPDATE_ID_WORKER，我想在WORKER中更改特定ID的值，并且还要在FIRM中具有特定ID值的所有实例中。

存储过程：

........
@id
..
？
........

解决方案

你不应该这样做，而是插入新的记录而不是更新它。
但是，如果你真的需要，你可以做到以下几点：

• 暂时禁用强制FK约束（例如 ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL ）
  
• 然后更新您的PK


• 然后更新您的FK以匹配PK更改
  
• 最后启用执行FK约束
  
  

  Here is my problem: I have 2 tables:

  1. WORKER, with columns |ID|OTHER_STAF| , where ID is primary key
  2. FIRM, with columns |FPK|ID|SOMETHING_ELSE| , where combination FPK and ID make primary key, and also ID is a foreign key referenced to WORKER.ID (not null, and must have same value as in WORKER).

  I want to make stored procedure UPDATE_ID_WORKER, where I would like to change the value of specific ID in WORKER, and also in all instances of specific value of ID in FIRM.

  stored procedure:

  ........ @id .. ???? ........

  解决方案

  You shouldn't really do this but insert in a new record instead and update it that way.
  But, if you really need to, you can do the following:

  • Disable enforcing FK constraints temporarily (e.g. ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)
  • Then update your PK
  • Then update your FKs to match the PK change
  • Finally enable back enforcing FK constraints

  这篇关于如何更新主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！