mysql - ＃1452-无法添加或更新子行:外键约束失败

Question

我正在尝试向表User中插入一些数据。 User表具有2个外键：StudentID和StaffID。

我希望能够输入StaffID或StudentID，它应该链接到相关的表（已经有StudentID或StaffID）。表User只能具有StaffID或StudentID。有人可以帮忙吗？

INSERT INTO `User` (`UserName`, `Email`, `StudentID`,`StaffID`,`Paasword`) 
VALUES ('phill', 'ph@lms.com', '', '2201','654321');

Answer 1

好，准备好自己这个答案很长，但是很彻底。

简短答案：

正如answer在此@HLGEM中所引用的那样，您可以通过在STAFF和STUDENT表中设置主键（假定值StaffID和StudentID为NULLABLE）来完成您要的内容。以下是他的回答的相关摘要：


  要在FK中允许空值，通常要做的就是在具有FK的字段上允许空值。空值与其作为FK的想法是分开的。


您可以在表定义中执行此操作，方法是指定以下内容，将NULL添加到您的create语句中，类似于以下内容：

CREATE TABLE STUDENT (
    StudentID INT UNSIGNED NULL PRIMARY KEY AUTO_INCREMENT
    ...
)

上面是如何将其应用于 STUDENT表的示例。 STAFF表将具有类似的方法。注意，这些额外的值是根据配置 ID字段时常见的常规配置提供的建议。

长答案：

正如@HLGEM在他的回答中提到的，在某些情况下，具有可以为 NULL的外键约束是适当的。但是，根据您的情况，它建议数据未完全标准化。只需少量表重构，就可以消除对 NULL外键的需要。让我们探索设计数据库表时的另一种可能性：

案例分析：

让我们从以下假设开始。由于您在问题中说过：


  我希望能够输入StaffID或StudentID，它们应该链接到相关表（已经具有StudentID或StaffID）。表用户只能具有StaffID或StudentID


可以肯定地说用户必须是工作人员或学生，但不能同时是两者。该假设为拥有 UserType表提供了强有力的用例。让我们更改 USER定义以支持 UserTypeId并创建 USER_TYPE表：

# USER TYPE Table Definition
CREATE TABLE USER_TYPES (
  UserTypeId TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  UserType VARCHAR(25) NOT NULL
) ENGINE=INNODB CHARSET=UTF8;

# USER TABLE Definition
CREATE TABLE USERS (
  UserId INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  Name VARCHAR(25) NOT NULL,
  Email VARCHAR(50) NOT NULL,
  Password VARCHAR(100) NOT NULL,
  UserTypeId TINYINT UNSIGNED NOT NULL,
  FOREIGN KEY(UserTypeId) REFERENCES USER_TYPES(UserTypeId)
) ENGINE=INNODB CHARSET=UTF8;

请注意，在这个新架构中，我们不再引用 StudentID或 StaffID，而是引用了 UserTypeId。这种方法有两个好处：


USERS。 UserTypeId是对 USER_TYPES。 UserTypeId的外键引用，但不再必须为NULLABLE。
您可以拥有更多的用户类型，而不仅仅是 Student或 Staff。


对于这里的案例研究，让我们创建两个用户类型 Student和 Employee（我将在这里解释为什么我不使用 Staff）。我们还继续用问题中提到的雇员 USERS的初始值填充 Phill表。

INSERT INTO USER_TYPES(UserType)
VALUES("Employee"),("Student");

INSERT INTO USERS(Name,Email,Password,UserTypeId)
VALUES("phill","ph@lms.com","654321",1);

优秀！我们的新设计很快就会融合在一起。现在让我们创建另外两个表，一个称为 STUDENTS，另一个称为 EMPLOYEES。在这种情况下，我选择使用 EMPLOYEES而不是 STAFF，因为它使您可以更加灵活地定义员工。在定义中将看到，您可以进一步定义 Employee的用户类型，其 ENUM值为 Faculty， Staff或 Administrative。将其视为常规类型 Employee的子类型。请注意，您也可以像我们为 USER_TYPES一样创建另一个联接表，例如一个名为 EMPLOYEE_TYPES的联接表。两种方法都合适。我选择使用 ENUM而不是另一个外键来演示如果您只有少数选择的话可以使用的其他概念。

接下来是最后两个表定义：

# STUDENTS Table Definition
CREATE TABLE STUDENTS(
  StudentId INT UNSIGNED PRIMARY KEY,
  Year ENUM('Freshman','Sophmore','Junior','Senior') NOT NULL,
  FOREIGN KEY(StudentId) REFERENCES USERS(UserId)
) ENGINE=INNODB CHARSET=UTF8;

# EMPLOYEES Table Definition
CREATE TABLE EMPLOYEES (
  EmployeeId INT UNSIGNED PRIMARY KEY,
  EmployeeType ENUM('Faculty','Staff','Administrative') NOT NULL,
  FOREIGN KEY(EmployeeId) REFERENCES USERS(UserId)
) ENGINE=INNODB CHARSET=UTF8;

请注意，这两个表没有自己的 Id列，而是将 Id表中的 USERS用作外键约束。这是有道理的，因为在成为学生或雇员之前，您必须先成为用户。

最后，让我们为 Phill添加一些员工数据：

INSERT INTO EMPLOYEES(EmployeeId,EmployeeType)
VALUES(1,"Faculty");

确实很多，但是现在您将开始收获好处。以上所有内容都是基础，因为它为您的数据库布局提供了完全标准化的方法，具有额外的灵活性，并且不需要NULLABLE外键。

在这种情况下，检索数据很容易，我们甚至不必知道 Phill是雇员还是学生。让我们看一个示例查询：

SELECT
u.UserId,
u.Name,
u.Email,
ut.UserType,
s.*,
e.*
FROM USERS AS u
INNER JOIN USER_TYPES AS ut ON u.UserTypeId = ut.UserTypeId
LEFT JOIN STUDENTS AS s ON u.UserId = s.StudentId
LEFT JOIN EMPLOYEES AS e ON u.UserId = e.EmployeeId
WHERE u.Email = "ph@lms.com";

返回：

+--------+--------+-------------+-----------+-----------+--------+------------+--------------+
| UserId | Name   | Email       | UserType  | StudentId | Year   | EmployeeId | EmployeeType |
+--------+--------+-------------+-----------+-----------+--------+------------+--------------+
|      1 | phill  | ph@lms.com  | Employee  | (null)    | (null) |          1 | Faculty      |
+--------+--------+-------------+-----------+-----------+--------+------------+--------------+

结论：

实时示例： sqlfiddle

所以你有它。通过在 LEFT JOIN， STUDENTS和 EMPLOYEES表之间执行 USERS，您将拉回两个表之一中存在的所有值以及所有默认用户值。从这里开始，很简单，只需检查 NULL或 StudentId上的 EmployeeId以确定您正在使用的用户类型。