在SQL中，如何在添加新记录时处理丢失的外键值？ [英] In SQL, how do I handle missing foreign key values when adding new records?

问题描述

请原谅这个问题的一般性质，但我很难找到关于如何处理这个问题的好建议。



假设我有两个SQL表，如下：



员工：id，name，department_id

1，'John'，1

2，'Mary'，3

3，'Cathy'，2

4，'Karen'，1



部门：id，姓名

1，'销售'

2，'管理员'
3，'车间'



当我创建新员工时，我想允许应用程序的用户将部门留空首先。但是当我创建数据库记录时，我显然必须提供 department_id 外键值。目前，我正在使用 -1 ，这永远不会是合法的外键。



我的问题来自于尝试编写SQL检索没有重复的所有独特员工。



如果我只是使用...

  SELECT  e.id，e.name，d.department  FROM 员工e  INNER   JOIN 部门d  ON  e。 department_id = d.id; 

...然后我没有得到任何新员工（即那些没有的员工）但被指派了一个部门）。这意味着应用程序的用户无法访问这些新员工并将他们放入部门。



如果另一方面，我使用...

  SELECT  e.id，e.name，d.department  FROM 员工e  INNER   JOIN 部门d  ON  e.department_id = d.id 或 e.department_id = -1; 

...我得到了很多重复记录，因为每个没有部门的员工都会在每个部门的结果中包含一次。



我真的很感激一些关于如何最好地解决这个问题的建议。



我尝试了什么：



我尝试添加一个虚拟部门作为第一条记录，并为此分配新员工，但是当我到时我必须从UI中过滤掉它做一些事情，比如提出一个不太理想的部门列表。

解决方案

更改 department_id 列Employees表可以为空，并使用 NULL 表示尚未分配部门。这将允许您在两个表之间创建外键引用。

  ALTER  表员工
  ALTER   COLUMN  department_id  int   NULL ; 
  GO  
 更新员工
  SET  department_id =  NULL  
  WHERE  department_id = -1; 
  GO  
  ALTER   TABLE 员工 WITH  检查 
  ADD   CONSTRAINT  FK_Employees_Department 
  FOREIGN   KEY （department_id）
  REFERENCES 部门（id）; 
  GO  
  ALTER   TABLE 员工
  CHECK   CONSTRAINT  FK_Employees_Department; 
  GO  

当您想要检索记录时，使用 LEFT JOIN ：

  SELECT  
 e.id，
 e.name，
 d.department 
  FROM  
员工 As  e 
  LEFT   JOIN 部门 As  d 
  ON  d.id = e.department_id 
; 

未分配部门的员工记录将为部门列返回 NULL 。< br $> b $ b

SQL连接的可视化表示 [ ^

Please forgive the general nature of this question, but I am struggling to find good advice on how to handle this problem.

Assume I have two SQL tables, as follows:

Employees: id, name, department_id
1, 'John', 1
2, 'Mary', 3
3, 'Cathy', 2
4, 'Karen', 1

Departments: id, name
1, 'Sales'
2, 'Admin'
3, 'Shop floor'

When I create a new employee, I would like to allow the user of the application to leave the department blank to begin with. But when I create the database record, I obviously have to supply a department_id foreign key value. At the moment, I am using -1, which can never be a legal foreign key.

My problem comes when trying to write the SQL to retrieve all the unique employees with no duplicates.

If I simply use...

SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id;

...then I don't get any of the new employees (i.e. the ones who have not yet been assigned a department). This means the user of the application can't access these new employees and put them in a department.

If on the other hand, I use...

SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id OR e.department_id = -1;

...I get lots of duplicate records because every employee with no department is included in the result once for each department.

I would really appreciate some advice on how best to tackle this.

What I have tried:

I have tried adding a dummy department as the first record and assigning new employees to that, but then I have to filter this out from the UI when I am doing things like presenting a list of departments, which seems less than ideal.

解决方案

Change the department_id column in the Employees table to be nullable, and use NULL to indicate that a department has not been assigned. This will allow you to create the foreign key reference between the two tables.

ALTER TABLE Employees
ALTER COLUMN department_id int NULL;
GO
UPDATE Employees
SET department_id = NULL
WHERE department_id = -1;
GO
ALTER TABLE Employees WITH CHECK
ADD CONSTRAINT FK_Employees_Department
FOREIGN KEY (department_id)
REFERENCES Departments (id);
GO
ALTER TABLE Employees
CHECK CONSTRAINT FK_Employees_Department;
GO

When you want to retrieve the records, use a LEFT JOIN:

SELECT 
    e.id, 
    e.name, 
    d.department
FROM
    Employees As e
    LEFT JOIN Departments As d
    ON d.id = e.department_id
;

Employee records with no department assigned will return NULL for the department column.

Visual Representation of SQL Joins[^]

这篇关于在SQL中，如何在添加新记录时处理丢失的外键值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！