PostgreSQL后端。参考。交易中的完整性违规。 [英] PostgreSQL back-end. Ref. integrity violation in transaction.

问题描述

大家好，


我已经将一个Access数据库架构迁移到PostgreSQL，我正试图让

一切都恢复正常。我遇到了以下问题...


在Access事务中，我在一个表中插入一行，然后在一个表中插入一行

行与第一条记录具有外键关系的表。

第二次插入失败，参考完整性违规引用了我提到的

相同的关系。这与JET后端和

相同的关系工作正常。


就好像我的2个插件没有使用相同的连接或什么，所以我的

第二次插入并没有看到第一个，因为它尚未实现。所有

语句都通过相同的DAO工作区执行。我正在使用DAO，因为我是处理异构数据的
，根据客户端JET表中的数据选择插入PostgreSQL

表。


请注意，我使用记录集来执行这些插入，因为这是获取新的AutoNumber键时最简单的方法，当JET是后端时，但是我

可以改变它，如果它会帮助，只需使用.Execute，因为我现在必须使用

传递查询来获得新的序列无论如何，使用PostgreSQL后端的值，

。

感谢任何人的建议，


- Steve J.

解决方案

Steve Jorgensen写道：

大家好，
我已经将Access数据库架构迁移到PostgreSQL，我正试图让一切都恢复正常。我遇到了以下问题...

在Access事务中，我在一个表中插入一行，然后在一个表中插入一个
行，该表具有外键关系第一条记录。
第二次插入失败，引用完整性违反，指的是我提到的相同关系。这与JET后端和
相同的关系工作得很好。

就好像我的2个插件没有使用相同的连接或什么的，所以我的
第二个插入物并没有看到第一个，因为它尚未实现。所有
语句都通过相同的DAO工作区执行。我正在使用DAO，因为我处理异构数据，根据客户端JET表中的数据选择插入PostgreSQL
表。
请注意，我使用记录集来执行这些插入操作，因为这是在JET作为后端时获取新的AutoNumber键的最简单方法，但是如果它有帮助我可以改变它，并且只使用.Execute，因为我现在必须使用
传递查询来获取PostgreSQL后端的新SERIES值，无论如何。



也许您应该将2个查询放在一个事务中？


workspace.begin

运行查询1

运行查询2

workspace.commit

MGFoster ::: mgf00< at> earthlink< decimal-point> net

奥克兰，加利福尼亚州（美国）

2004年2月7日星期六00:38:36 GMT，MGFoster< [e-mail]>写道：

Steve Jorgensen写道：

大家好，

我已经迁移了一个Access数据库模式到PostgreSQL，我正试着让一切都恢复正常。我遇到了以下问题...

在Access事务中，我在一个表中插入一行，然后在一个表中插入一个
行，该表具有外键关系第一条记录。
第二次插入失败，引用完整性违反，指的是我提到的相同关系。这与JET后端和
相同的关系工作得很好。

就好像我的2个插件没有使用相同的连接或什么的，所以我的
第二个插入物并没有看到第一个，因为它尚未实现。所有
语句都通过相同的DAO工作区执行。我正在使用DAO，因为我处理异构数据，根据客户端JET表中的数据选择插入PostgreSQL
表。
请注意，我使用记录集来执行这些插入操作，因为这是在JET作为后端时获取新的AutoNumber键的最简单方法，但是如果它有帮助我可以改变它，并且只使用.Execute，因为我现在必须使用
传递查询来获取PostgreSQL后端的新SERIES值，无论如何。


workspace.begin
运行查询1
运行查询2
workspace.commit

MGFoster ::: mgf00< at> earthlink< decimal-point> net
奥克兰，加利福尼亚州（美国）

我已经在一个交易中做了所有事情，除非Access是

以某种方式阻止这个例如，打开记录集的第二个连接。

星期五，2004年2月6日01:30:19 GMT，Steve Jorgensen

<无**** @ nospam.nospam>写道：

我已经将Access数据库架构迁移到PostgreSQL，我正试图让一切都恢复正常。我遇到了以下问题...

在Access事务中，我在一个表中插入一行，然后在一个表中插入一个
行，该表具有外键关系第一条记录。
第二个插入失败，引用完整性违反，指的是我提到的相同关系。

你需要提交的几率很高

执行第二次（两次交易）之前的第一次插入，或者切换到SQL并延迟

约束检查。 （我不确定PostgreSQL是否支持

延迟约束 - 你必须检查文档。）


-

Mike Sherrill

信息管理系统

Hi all,

I''ve migrated an Access database schema to PostgreSQL, and I''m trying to get
everything working again. I''m having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It''s as if my 2 inserts are not using the same connection or something, so my
second insert doesn''t "see" the first one since it''s not committed yet. All
statements are executed via the same DAO workspace. I''m using DAO because I''m
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

Note that I am using recordsets to do these inserts because this was the
easiest way to get the new AutoNumber keys when JET was the back-end, but I
can change that if it''ll help, and just use .Execute since I now have to use a
pass-through query to get the new SERIES values with the PostgreSQL back-end,
anyway.

Thanks for any advice anyone has,

- Steve J.

解决方案

Steve Jorgensen wrote:

Hi all,

I''ve migrated an Access database schema to PostgreSQL, and I''m trying to get
everything working again. I''m having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It''s as if my 2 inserts are not using the same connection or something, so my
second insert doesn''t "see" the first one since it''s not committed yet. All
statements are executed via the same DAO workspace. I''m using DAO because I''m
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

Note that I am using recordsets to do these inserts because this was the
easiest way to get the new AutoNumber keys when JET was the back-end, but I
can change that if it''ll help, and just use .Execute since I now have to use a
pass-through query to get the new SERIES values with the PostgreSQL back-end,
anyway.

Perhaps you should put the 2 queries inside one transaction?

workspace.begin
run query 1
run query 2
workspace.commit
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

On Sat, 07 Feb 2004 00:38:36 GMT, MGFoster <[e-mail]> wrote:

Steve Jorgensen wrote:

Hi all,

I''ve migrated an Access database schema to PostgreSQL, and I''m trying to get
everything working again. I''m having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It''s as if my 2 inserts are not using the same connection or something, so my
second insert doesn''t "see" the first one since it''s not committed yet. All
statements are executed via the same DAO workspace. I''m using DAO because I''m
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

Note that I am using recordsets to do these inserts because this was the
easiest way to get the new AutoNumber keys when JET was the back-end, but I
can change that if it''ll help, and just use .Execute since I now have to use a
pass-through query to get the new SERIES values with the PostgreSQL back-end,
anyway.

Perhaps you should put the 2 queries inside one transaction?

workspace.begin
run query 1
run query 2
workspace.commit
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

I am doing everything inside one transaction already, unless Access is
thwarting this somehow by, say, opening a second connection for the recordset.

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

I''ve migrated an Access database schema to PostgreSQL, and I''m trying to get
everything working again. I''m having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned.

Odds are good that you need to either commit the first insert before
executing the second (two transactions), or switch to SQL and deferred
constraint checking. (I''m not sure whether PostgreSQL supports
deferred constraints--you''d have to check the docs.)

--
Mike Sherrill
Information Management Systems

这篇关于PostgreSQL后端。参考。交易中的完整性违规。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！