oracle 10.2 用外键更新表

Update Table with foreign key in oracle 10.2

提问by Manish Sasmal

I have one table ROUTE_DETAILSwith column ROUTE_NUMBERand ROUTE_NAME.

我有一张ROUTE_DETAILS带有列ROUTE_NUMBER和ROUTE_NAME.

I have another table CUSTOMER_DETAILSwith column CUST_CODE, CUST_NAME, ROUTE_NUMBER. Here route number is the foreign key of ROUTE_DETAILS.

我有另一个CUSTOMER_DETAILS带有列的表CUST_CODE, CUST_NAME, ROUTE_NUMBER。这里的路由号是 的外键ROUTE_DETAILS。

ROUTE_DETAILSis parent table and CUSTOMER_DETAILSis child table.

ROUTE_DETAILS是父表，CUSTOMER_DETAILS是子表。

Data in ROUTE_DETAILS:

数据在ROUTE_DETAILS：

ROUTE_NUMBER        ROUTE_NAME
RN0001            ROUTE1
RN0002            ROUTE2
RN                ROUTE3

Data in CUSTOMER_DETAILS:

数据在CUSTOMER_DETAILS：

CUST_CODE        CUST_NAME       ROUTE_NUMBER
CC0001         CUSTOMER1        RN0001
CC0002         CUSTOMER2        RN

Now the problem is when I am trying to update ROUTE_NUMBERfrom ROUTE_DETAILSor update ROUTE_NUMBERfrom CUSTOMER_DETAILSan error is shown: integrity constraints violation child record found

现在的问题是，当我试图更新ROUTE_NUMBER的ROUTE_DETAILS或更新ROUTE_NUMBER的CUSTOMER_DETAILS错误显示：完整性约束违规的孩子找到记录

Query is:

查询是：

update ROUTE_DETAILS 
   set ROUTE_NUMBER = 'RN0003' 
 where ROUTE_NUMBER = 'RN'

same thing happen when I am trying to update customer_details.

当我尝试更新 customer_details 时也会发生同样的事情。

回答by a_horse_with_no_name

The only option I see is to change the FK constraint to "DEFERRABLE".

我看到的唯一选择是将 FK 约束更改为“DEFERRABLE”。

Then you can change both rows in a single transaction as the constraint is checked when you commit the data:

然后您可以在单个事务中更改两行，因为在提交数据时检查约束：

update ROUTE_DETAILS set ROUTE_NUMBER ='RN0003' where ROUTE_NUMBER ='RN';
update CUSTOMER_DETAILS set ROUTE_NUMBER ='RN0003' where ROUTE_NUMBER ='RN';
commit;

See the manual for details on how to change the FK constraint.

有关如何更改 FK 约束的详细信息，请参阅手册。

You if you set the constraint to "INITIALLY IMMEDIATE" you need to run set constraints deferredbefore running the updates.

如果将约束设置为“初始立即”，则需要set constraints deferred在运行更新之前运行。

Edit, here is a complete example:

编辑，这里是一个完整的例子：

Setup table and constraints:

设置表和约束：

create table route_details
(
 route_number varchar(20) not null primary key
);

create table customer_details
(
   cust_code varchar(20) not null primary key,
   route_number varchar(20) not null
);

alter table customer_details
  add constraint fk_route_number 
     foreign key (route_number)
     references route_details (route_number)
  deferrable
  initially immediate;


insert into route_details (route_number)
values ('RN0001');

insert into route_details (route_number)
values ('RN');

insert into customer_details (cust_code, route_number)
values ('CC0001', 'RN0001');

insert into customer_details (cust_code, route_number)
values ('CC0002', 'RN');

commit;

Run the update:

运行更新：

set constraints all deferred;

update ROUTE_DETAILS set ROUTE_NUMBER ='RN0003' where ROUTE_NUMBER ='RN';
update CUSTOMER_DETAILS set ROUTE_NUMBER ='RN0003' where ROUTE_NUMBER ='RN';
commit;

回答by Justin Harvey

Yes, you cannot update the parent primary key if a child is referencing that key value.

是的，如果子项引用该键值，则您无法更新父主键。

Also you cannot update the child to have a foreign key value that does not reference a primary key value in the parent table.

此外，您不能将子项更新为具有不引用父表中主键值的外键值。

So, you can either:

因此，您可以：

1)Relax the constraint temporarily while you make your changes, being sure to re-apply it afterwards.

1) 在您进行更改时暂时放松约束，确保之后重新应用它。

Or

或者

2)Delete the child row, update the parent row, then re-insert the child with the new foreign key value.

2）删除子行，更新父行，然后用新的外键值重新插入子行。

回答by Joe

Two thoughts on this...

关于这个的两个想法...

Firstly you should not be changing the value of your primary key, the whole point of a primary key is that it does not change.

首先，您不应该更改主键的值，主键的全部意义在于它不会更改。

If however you must make this change, there are two ways I know of to do it.

但是，如果您必须进行此更改，我知道有两种方法可以做到。

1. Insert 'RN0003' as a new row in route_details. Then update all affected customers. Then delete the RN row. So:

   insert into route_number values ('RN0003','ROUTE3');

   update customer_details set route_number = 'RN0003' where route_number = 'RN';

   delete from route_details where route_number = 'RN';

2. Use deferred keys as described here:

1. 在 route_details 中插入“RN0003”作为新行。然后更新所有受影响的客户。然后删除 RN 行。所以：

   插入 route_number 值 ('RN0003','ROUTE3');

   更新 customer_details set route_number = 'RN0003' where route_number = 'RN';

   从 route_details 中删除 route_number = 'RN';

2. 使用这里描述的延迟键：

http://www.orafaq.com/wiki/Foreign_key

http://www.orafaq.com/wiki/Foreign_key