与外键引用约束删除行时，用户友好的错误消息 [英] User-friendly error messages when removing row with Foreign Key REFERENCE constraint

问题描述

什么是处理去除分贝行有FK参考约束的最佳做法？我的目标是present更加用户友好的错误信息给最终用户。请注意，我不 T要删除部门与员工和我不要 T要对表的级联删除。

What would be best practice to handle removal of db row that has FK REFERENCE constraint? My goal was to present more user-friendly error messages to the end-user. Note that I dont want to delete department with employees and that I dont want to have cascade delete on tables.

例如，如果我们有两个表：

For example if we have two tables:

-- Department table
CREATE TABLE [dbo].[Department](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-- Employee table
CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DepartmentId] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]

如果一个人想从部门表，在该行的员工表中引用删除一行。我应该怎么样呢？

And if one wants to delete row from department table, where this row is referenced in employee table. What should one do?

1. 执行DELETE语句，检查是否排在EMPLOYEE表引用，并优雅地返回错误的GUI（与eployee列表中，如果需要的话）之前，

1. Before executing DELETE statement, check if row is referenced in employee table and gracefully return error to GUI (with eployee list if necessary)

执行DELETE语句，并做catch异常，如：

Execute DELETE statement and do catch exception like:

catch (SqlException ex) 
{ 
    switch (ex.Number) 
       case 547: HandleErrorGracefully()
} 

一些其他的方式？

Some other way?

将是很好，如果有人有code /链接到应用程序示例...

Would be nice if someone have code/link to application sample ...

推荐答案

选项3：两者都做1和2

Option 3: Do both 1 and 2

有有人在检查（通过）和删除失败之间的另一个过程中插入的潜力。

There is the potential for someone to insert in another process between the check (passes) and the delete failing.

在这种情况下，你可以说对不起，出事了以用户（而记录它），看看他们是否想再试一次。然后检查西港岛线拦截。

In this case, you can just say "sorry, something went wrong" to the user (but log it), and see if they want to try again. Then the check wil intercept it.

这篇关于与外键引用约束删除行时，用户友好的错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！