在SQL中，如何在添加新记录时处理丢失的外键值？

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 。 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_id1, 'John', 12, 'Mary', 33, 'Cathy', 24, 'Karen', 1Departments: id, name1, '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 EmployeesALTER COLUMN department_id int NULL;GOUPDATE EmployeesSET department_id = NULLWHERE department_id = -1;GOALTER TABLE Employees WITH CHECKADD CONSTRAINT FK_Employees_DepartmentFOREIGN KEY (department_id)REFERENCES Departments (id);GOALTER TABLE EmployeesCHECK CONSTRAINT FK_Employees_Department;GOWhen you want to retrieve the records, use a LEFT JOIN:SELECT e.id, e.name, d.departmentFROM 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中，如何在添加新记录时处理丢失的外键值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！