Has anyone had experience using foreign key constraints in SQL Server (or possibly Oracle) with their PRO-IV application? We have moved our application from ISAM into SQL Server but as of now don't use foreign key constraints in the database. Obviously, to do this, we would first need to make sure there are no "orphan" rows when the database is changed. I'm wondering if anyone has gone through this process and whether there are any PRO-IV specific issues to worry about.
Thanks. Mark Dexter
1 reply to this topic
#2
Richard Bassett
-
- Members
-
- 701 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 12 February 2007 - 01:40 PM
I haven't used SQL Server, but one thing to watch out for when trying to apply such constraints retrospectively to an existing application is WHEN the constraint is checked.
Some databases support deferring a check until commit time which may seem reasonable since the net effect of all the modifications made by a transaction should be to transition the database from one consistent state to another. However, this can be problematic since a lot of work must be tracked and deferred in the case of long-running transactions. I have actually never seen commit-time checks applied in a real life application.
The alternative is that the constraint is checked at write time, this would be whenever the tables involved in the relationship are written. This constrains the order in which it is legitimate to write records and may break existing applications. For example a transaction that creates parent AND child records must physically write the parent record before any child records if a constraint requires child rows to have a parent. I imagine a simple, "obvious" use of nested LS/R/Us in ProIV that worked fine for ISAM implementations will not do this for example.
A scenario where you wanted to insist that every child row in some relationship must have a parent AND every parent row must have at least one child would obviously pose an interesting problem..
YMMV
Some databases support deferring a check until commit time which may seem reasonable since the net effect of all the modifications made by a transaction should be to transition the database from one consistent state to another. However, this can be problematic since a lot of work must be tracked and deferred in the case of long-running transactions. I have actually never seen commit-time checks applied in a real life application.
The alternative is that the constraint is checked at write time, this would be whenever the tables involved in the relationship are written. This constrains the order in which it is legitimate to write records and may break existing applications. For example a transaction that creates parent AND child records must physically write the parent record before any child records if a constraint requires child rows to have a parent. I imagine a simple, "obvious" use of nested LS/R/Us in ProIV that worked fine for ISAM implementations will not do this for example.
A scenario where you wanted to insist that every child row in some relationship must have a parent AND every parent row must have at least one child would obviously pose an interesting problem..
YMMV
Nothing's as simple as you think
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
