带有使用外键的值的 Oracle Sql 更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1393257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Sql Update with values using foreign key?
提问by Passionate programmer
Table 1:
Name, x1-X2, fk1, fk2.
Table 2:
K1(parent for table 1),X
How to update table 1 whose second column x1-x2 depands on fk1,fk2 from Table 2
如何更新表 1，其第二列 x1-x2 依赖于表 2 中的 fk1,fk2
Table1:
a,1.0,1,2
b,-3.0,2,3
Table 2
1,4.0
2,5.0
3,2.0
回答by Vincent Malgrat
With this setup:
使用此设置：
CREATE TABLE table2 (k NUMBER PRIMARY KEY, x NUMBER);
CREATE TABLE table1 (
NAME VARCHAR2(10) PRIMARY KEY,
diff NUMBER,
fk1 NUMBER REFERENCES table2,
fk2 NUMBER REFERENCES table2);
the following update will "refresh" the colum table1.diffwith the values of table2:
以下更新将table1.diff使用以下值“刷新”列table2：
SQL> UPDATE (SELECT child.diff old_diff, parent2.x - parent1.x new_diff
2 FROM table1 child
3 JOIN table2 parent1 ON (child.fk1 = parent1.k)
4 JOIN table2 parent2 ON (child.fk2 = parent2.k))
5 SET old_diff = new_diff
6 WHERE old_diff != new_diff
7 OR (old_diff IS NULL AND new_diff IS NOT NULL)
8 OR (old_diff IS NOT NULL AND new_diff IS NULL);
Only the rows that need updating will be refreshed (thanks to the where clause).
只有需要更新的行才会被刷新（感谢 where 子句）。
回答by Chris Cameron-Mills
Not quite sure I understand the question, referential integrity constraints do not prevent you from updating a table. If you need to do a lot of work in a transaction you can look into Deferred Constraints. Is there any chance you could clarify what you mean?
我不太确定我理解这个问题，参照完整性约束不会阻止你更新表。如果您需要在事务中做大量工作，您可以查看Deferred Constraints。你有没有机会澄清你的意思？
回答by Thilo
Not sure exactly what the problem is, maybe you need to rephrase the question a little.
不确定到底是什么问题，也许您需要稍微改写一下问题。
In general, the foreign key constraint makes sure that there exists a corresponding row in the referenced table.
通常，外键约束确保引用的表中存在对应的行。
When you update a row with a foreign key, and try to set a value that does not point to such a master row, you will get an error, either immediately or when you commit (depending on when the constraint is enforced, it can be deferred).
当您使用外键更新一行，并尝试设置一个不指向此类主行的值时，您将立即或在提交时收到错误（取决于强制执行约束的时间，它可以是延期）。
Other than that, the update is no different than any other update.
除此之外，此更新与任何其他更新没有什么不同。
回答by Tom Hubbard
Since the data in table 1 is dependent on the data in table 2 you won't be able to "Update" table 1 directly.
由于表 1 中的数据依赖于表 2 中的数据，因此您将无法直接“更新”表 1。
You will have to perform the update on table 2 and then recalculate table 1.
您必须对表 2 执行更新，然后重新计算表 1。
You could do it inside a transaction or perhaps a trigger on table 2.
您可以在事务或表 2 上的触发器中执行此操作。
Another option may be to have table one only hold the foreign keys and the name and then create a view that calculates the X1-X2 value.
另一种选择可能是让 table one 只保存外键和名称，然后创建一个计算 X1-X2 值的视图。
EDIT
编辑
After looking at the sample data, I don't think you can unambiguously have table 2 be updates as a result of an update on table 1.
查看示例数据后，我认为您无法明确地将表 2 更新为表 1 的更新结果。
For example if you update the second column of table 1 to be 43, how would you know what values to set the specific rows of table 2 (it could be 40 and 3, 20 and 23, etc.)
例如，如果您将表 1 的第二列更新为 43，您如何知道将表 2 的特定行设置为哪些值（可能是 40 和 3、20 和 23 等）？
