如何更新具有外键限制的两个表的行 [英] How to update rows of two tables that have foreign key restrictions

问题描述

我有两张表:一张是国外参考表，比如table a，另一个是数据表，比如table b.现在，当我需要更改table b 中的数据时，却受到table a 的限制.如何在不收到此消息的情况下更改两个表中的rid"?

<块引用>

错误:插入或更新表表a"违反外键约束fk_boo_kid"SQL 状态:23503

详细信息:键 (kid)=(110) 不存在于表表 b"中.

更新两个表的示例查询:

UPDATE table b table a SETrid = 110 WHERErid =1

<前>表b+-----+-------+-------+|摆脱|骑|昆塔 |+-----+-------+-------+|1 |汽车 |1 ||2 |自行车|1 |+-----+-------+-------+表一+-----+-----+-----+|孩子 |摆脱|日期 |+-----+-----+-----+|1 |1 |20-12-2015 ||2 |2 |20-12-2015 |+-----+-----+-----+

解决方案

在 Postgres 中，您可以使用可写 CTE 在单个语句中更新两个表.

假设这个表设置:

create table a (rid integer主键，ride text，qunta integer)；创建表b(孩子整数主键，删除整数引用a，日期日期)；

CTE 将是:

with new_a as (更新一个摆脱 = 110其中rid = 1)更新 b摆脱 = 110其中rid = 1;

由于(不可延迟的)外键是在语句级别评估的，并且主键和外键都在同一个语句中更改，所以这是有效的.

SQLFiddle:http://sqlfiddle.com/#!15/db6d1/1

I have two tables: one is foreign reference table lets say table a and other one is the data table lets say table b. Now, when I need to change the data in table b, but I get restricted by table a. How can I change "rid" in both tables without getting this message?

"ERROR: insert or update on table "table a" violates foreign key constraint "fk_boo_kid" SQL state: 23503

Detail: Key (kid)=(110) is not present in table "table b".

Example query to update both tables:

UPDATE table b table a SET rid = 110 WHERE rid =1

table b 
+-----+-------+-------+
| rid | ride  | qunta |
+-----+-------+-------+
|   1 |  car  |     1 |
|   2 |  bike |     1 |
+-----+-------+-------+  

table a
+-----+-----+------------+
| kid | rid |    date    |
+-----+-----+------------+
|   1 |   1 | 20-12-2015 |
|   2 |   2 | 20-12-2015 |
+-----+-----+------------+

解决方案

In Postgres you can use a writeable CTE to update both tables in a single statement.

Assuming this table setup:

create table a (rid integer primary key, ride text, qunta integer);
create table b (kid integer primary key, rid integer references a, date date);

The CTE would be:

with new_a as (
  update a 
    set rid = 110
  where rid = 1
)
update b 
  set rid = 110 
where rid = 1;

As (non-deferrable) foreign keys are evaluated on statement level and both the primary and foreign key are changed in the same statement, this works.

SQLFiddle: http://sqlfiddle.com/#!15/db6d1/1

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