Communicating with Informix from SQL Server

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 n开发者_开发技巧on-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...

• Yuck!
• Bad Luck (on the mess you've inherited)!
• Good Luck (with your work fixing the mess)!

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):

• No

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.