从 SQL Server 与 Informix 通信
是的...我有一个程序正在进行维护。
呃。即使描述它也让我不寒而栗……好吧，好吧。
每天晚上，运行在我们认为的 SQL Server 2000 上的数据库都会连接到 Informix 数据库并将其复制到 SQL Server 中。
Informix/SQL 数据由我正在维护的程序访问，然后该程序将一些数据存储在不同的 SQL Server 2000 数据库中。该数据应该对 Informix 数据具有外键约束，但事实并非如此。
接下来，来自 SQL 数据库的数据被放回 Informix/SQL 数据库，稍后又放回实际的 Informix 数据库。
基本上，我的问题的根源是非 Informix SQL 数据库上没有外键或主键约束。好吧，有些表在无意义的“ID”列上有一个主键，但这些表不会与任何其他表进行 FK。
我的问题是：是否可以以某种方式将 SQL Server 2000 链接到本机 Informix 数据库，以便我可以在 SQL 数据库中添加外键约束，以便 SQL Server 仅在可以引用现有行时才能创建行Informix 数据库中的行？
我会尽力回答任何人提出的任何问题，但据我所知，这些设计决策背后的推理是真正的疯狂，所以原因不会特别公开，因为我自己无法解决这些问题...
Right... I've got a program I'm doing some maintenance on.
Urgh. Even describing it makes me shudder... Right, okay.
Every night, a database running on what we think is SQL Server 2000 hooks up to an Informix database and copies it over into SQL Server.
The Informix/SQL data is accessed by the program I'm maintaining, which then stores some data in a different SQL Server 2000 database. This data should have foreign key constraints on the Informix data, but doesn't.
Further on down the line, data from the SQL database is put back into the Informix/SQL database, and later still, back into the actual Informix database.
Basically, the root of my problem is that there are no foreign or primary key constraints on the non-Informix SQL database. Well, some of the tables have a Primary key on a non-meaningful "ID" column, but those aren't FK'd to any other tables.
My question is: Is it possible to link SQL Server 2000 to the native Informix database in some way, so that I can add foreign key constraints within the SQL database so that SQL Server can only create rows when it can refer to existing rows within the Informix database?
I'll do my best to answer any questions anyone has, but as far as I can tell the reasoning behind these design decisions was genuine insanity, so reasons won't be particularly forthcoming, as I can't work them out, myself...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Informix 的哪个版本以及它运行在什么平台（机器类型、操作系统）上？
是否有其他原因导致您无法更新 Informix 模式来强制实施真正的 RI 约束（除了因为数据混乱而导致崩溃之外）。但您可能需要知道混乱程度如何，以便开始清理过程。 IDS (Informix Dynamic Server) 确实有“违规表”，可用于跟踪有问题的数据行 - “START VIOLATIONS”和“STOP VIOLATIONS”是在 Informix Guide to SQL: Syntax 手册中查找的语句。在启用违规检查的情况下开始加载数据之前，需要从一张表中卸载并删除数据。
澄清后，问题似乎是“我可以对 SQL Server 数据库中受（引用）Informix 数据库中的表约束的表设置引用完整性约束吗？”
答案是（遗憾的是）：
大多数 DBMS 都不愿意具有跨数据库引用完整性约束，更不用说跨 DBMS 约束了。
最接近的近似方法是在 SQL Server 数据库中拥有相关 Informix 表的副本，但这可能会增加数据传输工作负载。 OTOH，清理数据可能需要 - 一旦数据更接近理智，以后可能会放松复制。它部分取决于所引用的 Informix 数据的波动性 - 向所引用的表添加或删除行的频率。
Which version of Informix, and what platform (type of machine, o/s) is it running on?
Is there a reason (other than it will break because the data is a mess) that you can't update the Informix schema to enforce the real RI constraints. But you probably need to know how bad the mess is so that you can start the cleanup process. IDS (Informix Dynamic Server) does have 'violations tables' which can be used to track problematic rows of data - 'START VIOLATIONS' and 'STOP VIOLATIONS' are the statements to look for in the Informix Guide to SQL: Syntax manual You might well need to unload and delete the data from one table before starting to load the data with the violations checking enabled.
After clarification, the question seems to be "Can I set up referential integrity constraints on tables in the SQL Server databases that are constrained by (refer to) tables in the Informix databases?"
The answer to that is (sadly):
Most DBMS are reluctant to have cross-database referential integrity constraints, let alone cross-DBMS constraints.
The closest approximation would be to have copies of the relevant Informix tables in the SQL Server databases, but that probably adds to the data transfer workload. OTOH, cleaning up the data probably requires that - it might be possible to relax that copying later, once the data is more nearly sane. It depends, in part, on the volatility of the referenced Informix data - how often are rows added or deleted to the referenced tables.