Jump to content


Photo
- - - - -

Foreign Key Constraints in SQL Server


1 reply to this topic

#1 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 09 February 2007 - 09:59 PM

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

#2 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • 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 (w00t)
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