我如何知道 SQL 数据库中的记录是否正在其他地方使用?

发布于 2024-08-09 15:48:50 字数 328 浏览 7 评论 0原文

有没有办法知道数据库中的一条记录正在被另一条记录使用?

以删除为例:当我创建一条 SQL 语句尝试删除 dbo.group 中的组时,出现以下错误:

DELETE 语句与 REFERENCE 约束“FK_MyTable”冲突。冲突发生在数据库“MyDB”、表“dbo.User”、列“Group_ID”中。

由于我有一个与该组具有外键关系的用户,因此我无法删除该组。我希望能够在运行删除语句之前知道该记录是否链接到其他记录。有办法做到这一点吗?

基本上我想向用户表明他们正在查看的记录是不可删除的。我不想尝试删除该记录。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

小情绪 2024-08-16 15:48:50

其他人建议检测依赖行的方法,但这样做的问题是存在竞争条件:如果您的测试没有发现依赖行,并且您尝试删除该组,则可能有另一个客户端应用程序在您的两个查询之间的短暂时刻。因此,您获得的有关该组未使用的信息可能会在您收到后立即过时。

更好的方法是尝试删除该组,并处理出现的任何错误。

这对于性能也更好,因为您不必运行任何 SELECT 查询来检查依赖行是否存在。


关于您的评论和编辑的问题:好的,很公平,使用此信息向用户提供提示是有意义的(例如显示“删除组”按钮或使按钮变灰)。

在这种情况下,您可以使用其他人的建议之一,例如查询 Users 表中的相关行数。如果您需要多个组的信息,我会执行一个查询,将 Groups 加入到 Users,然后按组 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;

这比为每个组运行单独的 SQL 查询来获取用户数量要好。

如果您不知道有多少表可能依赖于Groups,则应该学习使用INFORMATION_SCHEMA 系统视图来查询元数据。我认为您的情况并非如此,所以我不会详细说明。

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.

源来凯始玺欢你 2024-08-16 15:48:50

执行一个查询,检查是否存在将 group_id 列设置为您要删除的 ID 的用户。如果查询返回 0 行,则可以毫无例外地删除

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

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]
爱,才寂寞 2024-08-16 15:48:50
  1. 您可以设置级联删除。

  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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文