I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework.
Originally, I was lead to believe that there wasn't much of a tie-in between the tables we are moving to SQL Server and the DB2 ones. However, I'm finding a few spots where there are foreign-key references across them.
Are there any standard ways of handling this kind of scenario, where we need to maintain foreign-key references across databases? I can think of a few options:
- Duplicate some of the data from SQL Server to DB2 on iSeries, enough to satisfy the foreign key constraints. I hate this idea not only because of the duplication of data but also because if we only duplicated the necessary data it would only exist to satisfy a constraint, but we couldn't do anything useful on it like JOIN to it.
- Remove the foreign key constraints from the iSeries and completely unlink the two databases. This has obvious issues, but thought I would throw it out there in case there are any benefits that I'm not seeing.
- Use some kind of database link to keep the two in sync. I know not how this would work, how it would perform, or even if it is possible to have foreign key constraints across a link. Perhaps this would need to be done with a trigger as I imagine a regular foreign key reference would not work in this case?
Does anybody see any other options, and what would be the best approach?
I don't believe there is anything on the DB2 side that would ever need to read/write anything to the SQL Server side, or vice versa, so we could keep the databases accesses from the application separate. I believe it is just to keep the foreign key references.
Best Answer
If the foreign key constraints don't point to any other table inside of originating database (ie, if SQL Server doesn't point to SQL Server, etc.), then I would drop those foreign keys. They aren't useful.
If they do point to other tables, then perhaps you do have to copy what data is needed just to satisfy the constraint. (Ugh). Either that or re-consider that relationship and possibly drop the foreign key constraint again.
Another thing to look into (at least on DB2 for i) would be informational constraints. I know DB2 LUW had them. They were non-enforced constraints. They helped the optimizer when choosing query plans, but were not enforced as an actual constraint (you could violate them). Of course, a side effect of those is that sometimes you could miss data you wanted due to the informational constraint.
And not sure if SQL Server has something similar or not. I am still learning that platform.