User-friendly error messages when removing row with Foreign Key REFERENCE constraint

Question

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. Before executing DELETE statement, check if row is referenced in employee table and gracefully return error to GUI (with eployee list if necessary)

2. Execute DELETE statement and do catch exception like:

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

3. Some other way?

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

Answer 1

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.

Answer 2

Be nicer!

Show the departments and number of employees in it, only enable delete option if number of employees is 0.

Rely on the referential integrity to stop coding errors, not user ones...

Answer 3

@gbn's answer is a good way to go. But you could also put ON DELETE CASCADE on the foreign key relation, and ask the user something like "Are you sure you want to delete this department and all its employees?".

Answer 4

I too had the same issue few days/months back. After lot of googling, I came to the resolution of translating error messages in DAL using SqlHelper class.

I handled this issue using the following way.

Created some classes that were derived from Exception class as shown in the diagram.

DalExceptionClass:Exception

ForeignKeyException: DalException

in SqlHelper

public void ExecuteNonQuery(string procedure)
{
    try
    {
      con.open()
       cmd.executenonquery();
      con.close();
      //Sql Exception is raised
    }
    catch(SqlException ex)
    {
        throw TranslateException(ex);
    }
}

using code in the link, I translated Sql Exceptions to DalException rather than Exception

protected DalException TranslateException(SqlException ex) { 
    DalException dalException = null; 

    // Return the first Custom exception thrown by a RAISERROR 
    foreach (SqlError error in ex.Errors) { 
        if (error.Number >= 50000) { 
            dalException = new DalException(error.Message, ex); 
        } 
    } 

    if (dalException == null) { 
        // uses SQLServer 2000 ErrorCodes 
        switch (ex.Number) { 
            case 2601: 
                // Unique Index/Constriant Violation 
                dalException = new DalUniqueConstraintException("{0} failed, {1} must be unique", ex); 
                break; 
            default: 
                // throw a general DAL Exception 
                dalException = new DalException(ex.Message, ex); 
                break; 
        } 
    } 

    // return the error 
    return dalException; 
} 

Please note case 2601:

I sent custom message here.

Now at my DAL, I will handle DalException only which will return me exception message as

"{0} failed, {1} must be unique" using string.Format(msg, "Insertion", "Client" );

this will send an exception to BAL as "Insertion failed, Client must be unique" which could somewhat be a granual message.

Edit

this will be handled at DAL as

    public Client Insert()
    {
    try
       {
        _repository.Insert(client);
       } 
    catch(DalException ex)
       {
//wrap message since, error will always be in client entity as client's method is being called. Also, insertion is being failed.
         throw new BusinessException(string.Format(ex.message), "Insertion", "Client");
       }
    }

References

How to manipulate SqlException message into user friendly message

http://www.reflectionit.nl/DAL.aspx

What should be the strategy to handle the sql exceptions?