How do I know if record from an SQL database is being used elsewhere?

Question

Is there a way to know that a record is being used by another record in the database?

Using deleting as an example: When I create an SQL statement trying to delete a group in dbo.group I get this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_MyTable". The conflict occurred in database "MyDB", table "dbo.User", column 'Group_ID'.

Since I have a user that has a foreign key relationship to the group I am not able to delete the group. I want to be able to know if the record is linked to other records before I run the delete statement. Is there a way to do that?

Basically I want to show the user that the record that they are viewing is undeleable. I do not want to try to delete the record.

Answer 1

Do a query which checks if there are users which have the column group_id set to the id you want to delete. If the query returns 0 rows you can delete without exception

SELECT count(group_id) 
FROM dbo.User
WHERE group_id = [yourgroupidtodeletevalue]

Answer 2

1. You can set up cascading deletes.

2. You can query the Forign Key table. The error tells you which table is dependent on the foreign key lookup.

Answer 3

Other folks are suggesting ways to detect dependent rows, but the problem with this is that there's a race condition: if your test finds no dependent rows, and you try to delete the group, there might be another client application that adds a dependent row in the brief moment between your two queries. So your information that the group is unused is potentially outdated as soon as you get it.

The better approach is to try to delete the group, and handle any errors that are raised.

This is also better for performance, because you don't have to run any SELECT query to check if the dependent rows exist.

---

Re your comment and edited question: Okay, fair enough, it makes sense to use this information to give hints to the user (e.g. display a "delete group" button or else gray out the button).

In that case, you can use one of the suggestions from other folks, such as query the count of dependent rows in the Users table. If you need information for multiple groups, I'd do one query, joining Groups to Users and then group by the group id.

SELECT g.groupid, COUNT(*) AS user_count
FROM dbo.Groups g JOIN dbo.Users u ON (g.groupid = u.groupid)
GROUP BY g.groupid;

That'd be better than running a separate SQL query for each group to get the count of users.

If you don't know how many tables may depend on Groups, you should learn to use the INFORMATION_SCHEMA system views to query for metadata. I don't think this is the case for you, so I won't go into detail.