添加约束或以其他方式阻止删除 SQL DB 表中的记录
我在 SQL Server 数据库中有一个表，其中包含我不想删除的行。这是应用程序按设计工作所需的行。
有没有办法向该行添加约束以防止其被删除？或者有另一种方法来处理这种情况？
I have a table in a SQL Server database that contains a row I never want deleted. This is a row required by the application to work as designed.
Is there a way to add a constraint to that row that prevents it from being deleted? Or another way to handle this scenario?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
以下是使用 FOR DELETE 触发器来防止在满足特定条件时删除行的示例：
Here is an example of using a FOR DELETE trigger to prevent the deletion of a row when a certain condition is satisfied:
如果您想防止意外删除，那么您可以使用一个虚拟表，使用
ON DELETE NO ACTION在您的表中声明一个外键，并在其中添加一行与您的“珍贵”行主键匹配的外键。这样，如果删除“父”行，引擎将拒绝并引发错误。如果您想防止故意删除，那么您应该依靠安全性（拒绝对表的 DELETE 权限）。当然，拥有所需权限的特权用户可以删除该行，没有办法阻止这种情况，您也不应该尝试。由于 SQL Server 不支持行级安全性，如果您只需要拒绝某些行，那么您必须返回到绘图并更改表布局，以便所有必须拒绝的行都被拒绝。存储在一个表中，允许删除的行存储在另一张表中。
其他解决方案（如触发器）最终将是这些主题的变体，您真正必须解决的是您是否要防止意外删除（可解决）或故意删除（无法解决，是他们的数据库，不是你的）。
If you want to prevent accidental deletes then you could have a dummy table that declares a foreign key into your table with
ON DELETE NO ACTION, and add one row in it with the foreign key matching your 'precious' row primary key. This way if the 'parent' row is deleted, the engine will refuse and raise an error.If you want to prevent intentional deletes then you should rely on security (deny DELETE permission on the table). Of course, privileged users that have the required permission can delete the row, there is no way to prevent that, nor should you try. Since SQL Server does not support row level security, if you need to deny only certain rows then you have to go back to the drawing broad and change your table layout so that all rows that have to be denied are stored in one table, and rows that are allowed to be delete are stored in a different table.
Other solutions (like triggers) will ultimately be a variation on these themes, what you really must solve is the question whether you want to prevent accidental deletes (solvable) or intentional deletes (unsolvable, is their database, not yours).
您可以通过多种方式来做到这一点，但这取决于具体情况。
如果表仅包含该行，则不要授予删除/截断权限。
如果表还包含其他行，您可以使用删除前触发器。
您将遇到的一个问题是，拥有数据库 DBA/SA 访问权限的人可以绕过您输入的任何内容（如果他们愿意），那么您想要防止什么、临时用户或任何人。
You could do it in a number of ways, although it depends on the situation.
If the table only contains that row, do not grant deletion / truncate privledges.
If the table contains other rows as well you could use a before deletion trigger.
One issue you will have is that someone with DBA / SA access to the database, can get around anything you put in, if they desire, so what are you trying to protect against, casual user, or anyone.