title: User-friendly error messages when removing row with Foreign Key REFERENCE constraint
body: <p>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 don<code>t want to delete department with employees and that I don</code>t want to have cascade delete on tables.</p>
  
  <p>For example if we have two tables:</p>
  
  <pre><code>-- 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]
  </code></pre>
  
  <p>And if one wants to delete row from department table, where this row is referenced in employee table. What should one do?</p>
  
  <ol>
  <li><p>Before executing DELETE statement, check if row is referenced in employee table and gracefully return error to GUI (with eployee list if necessary)</p></li>
  <li><p>Execute DELETE statement and do catch exception like: </p>
  
  <pre><code>catch (SqlException ex) 
  { 
      switch (ex.Number) 
         case 547: HandleErrorGracefully()
  } 
  </code></pre></li>
  <li><p>Some other way?</p></li>
  </ol>
  
  <p>Would be nice if someone have code/link to application sample ...</p>