CODEWITHSUNDEEP
sqlsql-server
Joel
Joel

Reputation: 203

How to get all references which hinders delete of a row I'm trying to delete

I have an Sql-Server database (2008R2, if that is important) with lots of tables. Some table contains foreign key references to other tables, and some of the foreign key constraints have cascade on delete, and some are restricted delete.

To be user friendly, in my application I want to figure out all other references that needs to be deleted before I can delete the one I'm currently deleting and present it to the user. Some generic problems described below:

  1. I try to delete from Customer where Id = 1 and Order has a foreign key to Customer (and that foreign key restricts user from deleting customer before orders are deleted), I would want to get a result of all Orders that restricts me from deleting Customer.

  2. If Contracts have references to Customer as well, and that foreign key is cascade, but Contract is referenced by Alarm with restricted delete, I want to get which Contract was responsible for not being able to delete Customer, and also the Alarms which was responsible for not being able to delete Customer.

  3. I want this behavior to be recursive, so that I get all connections, direct or indirect which hindered me from deleting Customer in the first place. I also want to be able to get this information wherever I start (No matter if I wanted to delete a Customer, Order, Alarm etc)

It feels like someone aught to have had similar problems before me, but I can mostly find information to get table->table foreign key restrictions, not in relation to a specific entity in the database (i.e. customer with Id = 1)

Is there any simple way of doing this?

Upvotes: 2

Views: 386

Answers (1)

Lawrence Thurman
Lawrence Thurman

Reputation: 677

basically I look at this as part of database/application architecture and you need to know as the developer or DBA how you tables are connected.

You need to use the customer ID and query against any table that uses customerID as a foreign key and that would give you a result.same with the contracts. You can do this with some ERD tools, but I tend to make my own classes to do this for my databases, this way I have total control of what I want to do. For instance in the delete method for a customer, a company may just want to disable or set active to false for the customer. or truly delete everything.

There is a way to cascade deletion in the database, but I am not sure you want to do that, since you are asking for a result to be returned.

Upvotes: 1

Related Questions