Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    66

    Question Unanswered: Conditional FK Deletes

    How would I use a Foreign Key to prevent deletions on the parent table?

    For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.

  3. #3
    Join Date
    Oct 2002
    Posts
    66
    Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

    Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

    Sorry if all this seems obvious, but I am a very green DBA....

    TIA,

    -Justin

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    You never mentioned cascading - this is very important.

    The answer is yes. SQL Server automatically sends this error to the calling application.

    Take a look at the books online "Cascading Referential Integrity Constraints" article.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •