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.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.|||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|||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.