Удаление сотрудника конфликтует с ограничением внешнего ключа руководителя отдела

Question

Удаление сотрудника конфликтует с ограничением внешнего ключа руководителя отдела

Я создал две таблицы, отделы и сотрудники со следующей структурой:

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

в таблице отделов уже было несколько сотрудников, которые уже являются менеджерами, и теперь, когда я пытаюсь удалить любого из этих сотрудников, я получаю сообщение об ошибке конфликта ограничений внешнего ключа, блокирующее оператор удаления. в чем проблема пожалуйста?

0

sql-server database foreign-key-relationship

Источник

user2218849 07 окт '15 в 05:50

1 ответ

Решение

Другие вопросы по тегам sql-server database foreign-key-relationship

user267 07 окт '15 в 06:35 2015-10-07 06:35 · Accepted Answer · 2015-10-07 06:35

Проблема в том, что вам не разрешено это делать.

Ограничение внешнего ключа - это ограничение целостности, означающее, что оно обеспечивает целостность данных в разных таблицах.

Если в одной строке отдела указано, что идентификатор сотрудника, являющегося руководителем этого отдела, равен 73, было бы не хорошо, если в базе данных нет сотрудника с идентификатором 73.

Ограничение внешнего ключа гарантирует, что этого не произойдет:

Вы не можете вставить или обновить строку отдела, чтобы иметь идентификатор менеджера, который не существует в таблице сотрудников
Вы не можете удалить сотрудника, на которого ссылаются как на руководителя, из строки отдела
Вы не можете обновить идентификатор сотрудника, на которого ссылаются как на менеджера, из строки отдела

Решение состоит в том, чтобы сначала исправить отдел:

Переключение на другого менеджера путем обновления строки отдела и корректировки идентификатора менеджера
NULL'ing out менеджер, говоря, что в этом отделе нет менеджера, если это разрешено

Выполнив любое из этих двух действий, вы сможете удалить сотрудника, при условии, что других ссылок на внешние ключи также нет.