postgresql 当一行有外键到另一行时，如何原子地写入两行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37380695/
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
How can two rows be written atomically when one row has a foreign key into the other?
提问by maxcountryman
I have a transaction where we insert a row of table fooand then a row of table bar. This ensures we either write both rows or neither. The trouble with this is barhas a foreign key into foo. Because we don't know the idof fooat the time of the barinsert, this fails the foreign key constraint.
我有一个事务，我们插入一行 table foo，然后插入一行 table bar。这确保我们要么写两行，要么都不写。这样做的问题是bar有一个外键进入foo. 因为我们在插入时不知道的id，所以外键约束失败。foobar
Previously I've used tools like SQLAlchemy, when writing Python backends, that include the capability of flushing a session before the transaction is committed--this allows the user to derive the idof fooand pass it along to the INSERTinto barbefore actually writing anything.
以前，我在编写 Python 后端时使用过像 SQLAlchemy 这样的工具，其中包括在提交事务之前刷新会话的功能——这允许用户在实际编写任何内容之前导出idoffoo并将其传递给INSERTinto bar。
My question is, in the context of JDBC and its Clojure wrapper, how can this be done?
我的问题是，在 JDBC 及其 Clojure 包装器的上下文中，如何做到这一点？
采纳答案by maxcountryman
Previously I was attempting to use (jdbc/query db-spec ["select id from foo where name='my_foo'"])within the transaction to derive the dependent foorow ID. This was returning niland so it seemed like the obvious method didn't work. However it turned out I was using db-specand not the transaction connection, which if you use jdbc/with-db-transactionis bound in the vector.
以前我试图(jdbc/query db-spec ["select id from foo where name='my_foo'"])在事务中使用来派生相关foo行 ID。这是回来了nil，所以看起来明显的方法不起作用。然而，事实证明我使用的是db-spec而不是事务连接，如果你使用jdbc/with-db-transaction它绑定在向量中。
For example:
例如：
(jdbc/with-db-transaction [t-conn db-spec]
(jdbc/insert! t-conn :foo {:name "my_foo"})
(jdbc/query t-conn ["select id from foo where name='my_foo'"]))
The queryin the above form will yield the correct row ID.
在query上述形式将产生正确的行标识。
回答by klin
You can insert values into both tables in one query, e.g.:
您可以在一个查询中将值插入到两个表中，例如：
create table foo (
foo_id serial primary key,
name text);
create table bar (
bar_id serial primary key,
foo_id int references foo,
name text);
with insert_into_foo as (
insert into foo (name)
values ('some foo')
returning foo_id
)
insert into bar (foo_id, name)
select foo_id, 'some bar'
from insert_into_foo;
回答by Craig Ringer
This is part of what DEFERRABLEforeign key constraintsare for.
这是DEFERRABLE外键约束的一部分。
ALTER TABLE mytable
DROP CONSTRAINT the_fk_name;
ALTER TABLE
ADD CONSTRAINT the_fk_name
FOREIGN KEY (thecol) REFERENCES othertable(othercol)
DEFERRABLE INITIALLY IMMEDIATE;
then
然后
BEGIN;
SET CONSTRAINTS DEFERRED;
INSERT thetable ...;
INSERT INTO othertable ...;
-- optional, but if you do this you get any errors BEFORE commit
SET CONSTRAINTS IMMEDIATE;
COMMIT;
I suggest using initially immediateand set constraintsso that the rest of the time you don't create queued triggers. It's better for performance and memory use, plus it won't confuse apps that don't understand and expect deferred cosntraints.
我建议使用initially immediateandset constraints以便在其余时间不创建排队触发器。它对性能和内存使用更好，而且它不会混淆不理解和期望延迟 cosntraints 的应用程序。
If your framework can't cope with this you can use DEFERRABLE INITIALLY DEFERREDinstead.
如果您的框架无法应对这一点，您可以DEFERRABLE INITIALLY DEFERRED改用。
