Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Conditional Foreign Key Constraint

Conditional Foreign Key Constraint

Conditional Foreign Key Constraint

I have database that handles maintenance records for buildings in various office parks around the country. The country is divide up into regions and one of the the office in one of these office parks is responsible for handling the records for all the office parks in a given region. There is a stored procedure that is used to "move" responsibility for work order records associated with a particular building from one region to the managing office located in another region. The problem occur when one work order applies to two buildings (do a preventive maintenance on the rooftop air conditioners in building a and building b), and management decides that responsibility for one of these buildings is to be shifted to another regional HQ. In this case, a new work order is created with the HQ Name field being changed to the receiving HQ for the one building involved. The original work order remains associated with the second building that isn't being "moved". Each work order has a Work Order Number which is unique within a region but could exist in the receiving region. In this case, a new work order number is assigned and a record is created in a history table so that if someone is trying to track work orders they can find out what happened to this work order. The problem arises when the second building is moved. It is assigned to the work order in the receiving HQ that was created when the firs building was "moved". The work order that was associated with the original regional HQ is then deleted and the history record remains.
Somewhere along the line, after this stored procedure was tested and working, there was a foreign key constraint added to the history table that prevents deleting the old work order record. The Change Reason field for the insert statement in the history was set up to be a particularly long phrase so that they could be found and there would not be any coincidental erroneous matches. Is there any way to get around this constraint and be able to delete any work order linked to the history table when the history contains this particular phrase? (There is no provision in the work order table to create a "soft" delete.)

RE: Conditional Foreign Key Constraint

I just noticed you didn't get an answer for over a month.

I'm not surprised. Your description isn't giving good enough insight about what's happening, what's wrong and what's expected.

I'll not even try to give an answer, perhaps you resolved the issue yourself, posted elsewhere and got answers there.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close