title: deleting an employee conflicts with department manager foreign key constraint
body: <p>I created two tables , departments and employees with the following structure :</p>
  
  <pre><code>CREATE TABLE [dbo].[departments](
      [department_id] [bigint] IDENTITY(10,10) NOT NULL,
      [department_name] [nvarchar](30) NOT NULL,
      [manager_id] [bigint] NULL,
      [location_id] [bigint] NULL,
      [department_notes] [varchar](150) NULL,
      [created_by] [bigint] NULL,
      [created_date] [datetime] NULL,
      [last_updated_by] [bigint] NULL,
      [last_updated_date1] [datetime] NULL,
      [status] [varchar](12) NOT NULL,
   CONSTRAINT [PK_departments] PRIMARY KEY CLUSTERED 
  (
      [department_id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   CONSTRAINT [UQ_NoRepeat] UNIQUE NONCLUSTERED 
  (
      [department_name] ASC,
      [location_id] ASC,
      [status] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  
  ALTER TABLE [dbo].[departments] ADD  CONSTRAINT [DF_departments_created_date]  DEFAULT (getdate()) FOR [created_date]
  GO
  
  ALTER TABLE [dbo].[departments] ADD  CONSTRAINT [DF_departments_created_date1]  DEFAULT (getdate()) FOR [last_updated_date1]
  GO
  
  ALTER TABLE [dbo].[departments] ADD  CONSTRAINT [DF_departments_status]  DEFAULT ('Active') FOR [status]
  GO
  
  ALTER TABLE [dbo].[departments]  WITH CHECK ADD FOREIGN KEY([manager_id])
  REFERENCES [dbo].[employees] ([employee_id])
  GO
  
  ALTER TABLE [dbo].[departments]  WITH CHECK ADD  CONSTRAINT [FK_departments_locations] FOREIGN KEY([location_id])
  REFERENCES [dbo].[locations] ([location_id])
  GO
  
  ALTER TABLE [dbo].[departments] CHECK CONSTRAINT [FK_departments_locations]
  GO
  
  ALTER TABLE [dbo].[departments]  WITH CHECK ADD  CONSTRAINT [Ck_deptStatus] CHECK  (([Status]='Deleted' OR [Status]='Active'))
  GO
  
  ALTER TABLE [dbo].[departments] CHECK CONSTRAINT [Ck_deptStatus]
  GO
  
  
  CREATE TABLE [dbo].[employees](
      [employee_id] [bigint] NOT NULL,
      [first_name] [nvarchar](20) NULL,
      [last_name] [nvarchar](25) NOT NULL,
      [email] [nvarchar](25) NOT NULL,
      [phone_number] [nvarchar](20) NULL,
      [hire_date] [date] NULL,
      [job_id] [nvarchar](10) NOT NULL,
      [salary] [numeric](8, 2) NOT NULL,
      [commission_pct] [numeric](2, 2) NULL,
      [manager_id] [bigint] NULL,
      [department_id] [bigint] NOT NULL,
      [allow_login] [bit] NOT NULL,
      [user_id] [nvarchar](128) NULL,
      [allow_email] [bit] NOT NULL,
      [driv_lic_no] [nchar](20) NULL,
      [reporting_to] [bigint] NULL,
      [salutation] [nchar](10) NULL,
      [date_of_birth] [date] NULL,
      [gender] [nchar](10) NULL,
      [blood_group] [nchar](10) NULL,
      [Nationality] [nchar](10) NULL,
      [gov_id] [nchar](25) NULL,
      [passport_no] [nchar](25) NULL,
      [passport_expir] [date] NULL,
      [driv_lic_expir] [date] NULL,
      [perm_address] [varchar](250) NULL,
      [perm_city] [varchar](50) NULL,
      [perm_state] [varchar](50) NULL,
      [per_zip] [nchar](20) NULL,
      [perm_country] [nvarchar](6) NULL,
      [current_address] [varchar](250) NULL,
      [current_city] [varchar](50) NULL,
      [current_state] [varchar](50) NULL,
      [current_zip] [nchar](20) NULL,
      [current_country] [nvarchar](6) NULL,
      [mobile_no] [nvarchar](20) NULL,
      [notes] [varchar](250) NULL,
      [added_by] [bigint] NULL,
      [added_on] [date] NULL,
      [send_cred_by_email] [bit] NOT NULL,
      [user_name] [nvarchar](256) NULL,
   CONSTRAINT [PK_employees] PRIMARY KEY CLUSTERED 
  (
      [employee_id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  
  ALTER TABLE [dbo].[employees] ADD  CONSTRAINT [DF_employees_allow_login]  DEFAULT ((0)) FOR [allow_login]
  GO
  
  ALTER TABLE [dbo].[employees] ADD  CONSTRAINT [DF_employees_allow_email]  DEFAULT ((0)) FOR [allow_email]
  GO
  
  ALTER TABLE [dbo].[employees] ADD  DEFAULT ('Mr.') FOR [salutation]
  GO
  
  ALTER TABLE [dbo].[employees] ADD  DEFAULT ((1)) FOR [send_cred_by_email]
  GO
  
  ALTER TABLE [dbo].[employees]  WITH CHECK ADD  CONSTRAINT [FK_employees_AspNetUsers] FOREIGN KEY([user_id])
  REFERENCES [dbo].[AspNetUsers] ([Id])
  GO
  
  ALTER TABLE [dbo].[employees] CHECK CONSTRAINT [FK_employees_AspNetUsers]
  GO
  
  ALTER TABLE [dbo].[employees]  WITH CHECK ADD  CONSTRAINT [FK_employees_countries] FOREIGN KEY([perm_country])
  REFERENCES [dbo].[countries] ([country_id])
  GO
  
  ALTER TABLE [dbo].[employees] CHECK CONSTRAINT [FK_employees_countries]
  GO
  
  ALTER TABLE [dbo].[employees]  WITH CHECK ADD  CONSTRAINT [FK_employees_countries1] FOREIGN KEY([current_country])
  REFERENCES [dbo].[countries] ([country_id])
  GO
  
  ALTER TABLE [dbo].[employees] CHECK CONSTRAINT [FK_employees_countries1]
  GO
  
  ALTER TABLE [dbo].[employees]  WITH CHECK ADD  CONSTRAINT [FK_employees_employees] FOREIGN KEY([employee_id])
  REFERENCES [dbo].[employees] ([employee_id])
  GO
  
  ALTER TABLE [dbo].[employees] CHECK CONSTRAINT [FK_employees_employees]
  GO
  </code></pre>
  
  <p>there were some existing employees which are already managers in departments table , now when I try to delete any of those manager employees, I get a foreign key constraint conflict error blocking the delete statement. 
  what is the problem please ?</p>