#1452 - 无法添加或更新子行:外键约束失败
我试图插入一些数据到表User
。 User
表有2个外键:StudentID
和StaffID
。#1452 - 无法添加或更新子行:外键约束失败
我希望能够输入StaffID
或StudentID
,该链接应该链接到相关表格(已经有StudentID
或StaffID
)。表User
只能有StaffID
或StudentID
。谁可以帮忙?
INSERT INTO `User` (`UserName`, `Email`, `StudentID`,`StaffID`,`Paasword`)
VALUES ('phill', '[email protected]', '', '2201','654321');
好吧,准备好自己吧。这个答案很长,但彻底。
答案很简单:
正如@HLGEM在此answer引用你可以完成你正在通过使主键在STAFF
和STUDENT
表,想必值StaffID
和StudentID
NULLABLE问。以下是他的回答中的相关片段:
要允许FK中的空值,通常您只需在具有FK的字段上允许空值。空值与作为FK的想法是分开的。
为此,您可以在您的表定义添加NULL
到您指定创建一个类似于下面的语句:
CREATE TABLE STUDENT (
StudentID INT UNSIGNED NULL PRIMARY KEY AUTO_INCREMENT
...
)
以上是如何将其应用到STUDENT
表的例子。 STAFF
表格也会有类似的方法。请注意,在配置ID
字段时,基于常规配置的常见配置会提供额外的值作为建议。
龙答:
由于@HLGEM在他的回答中提到,有一些时候是适当的有一个外键约束,可以是NULL
。但是,就你而言,它建议数据没有完全标准化。在你的情况下,需要一个NULL
外键可以消除一个小表重构。让设计数据库表时的探索另一种可能性:
案例研究:
让我们用下面的假设开始。因为你在你的问题中提到:
我希望能够输入应该链接到相关表格(已经有StudentID或StaffID)的StaffID或StudentID。该表用户只能有,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
在这里解释)。让我们继续前进,并使用Phill
的初始值填充USERS
表,您的员工是您在问题中提到的。
INSERT INTO USER_TYPES(UserType)
VALUES("Employee"),("Student");
INSERT INTO USERS(Name,Email,Password,UserTypeId)
VALUES("phill","[email protected]","654321",1);
非常好!我们的新设计快速融合在一起。现在让我们创建另外两个表,一个叫STUDENTS
,另一个叫EMPLOYEES
。在这种情况下,我选择EMPLOYEES
而不是STAFF
,因为它可以让您更灵活地定义员工。正如您在定义中所看到的,您可以使用Faculty
,Staff
或Administrative
的ENUM
值进一步定义Employee
的用户类型。把它看作一般类型的子类型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
列,而是从USERS
表引用Id
的外键约束。这是有道理的,因为您必须是用户,然后才能成为学生或员工。
最后,让我们添加一些员工数据Phill
:
INSERT INTO EMPLOYEES(EmployeeId,EmployeeType)
VALUES(1,"Faculty");
这是很多,但现在你会开始从中获益。上述所有内容都是基础性的,因为它提供了一种完全规范化的数据库布局方法,具有更高的灵活性,并且不需要外键。
在这种情况下检索数据是很容易,我们甚至不知道是否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 = "[email protected]";
返回:
+--------+--------+-------------+-----------+-----------+--------+------------+--------------+
| UserId | Name | Email | UserType | StudentId | Year | EmployeeId | EmployeeType |
+--------+--------+-------------+-----------+-----------+--------+------------+--------------+
| 1 | phill | [email protected] | Employee | (null) | (null) | 1 | Faculty |
+--------+--------+-------------+-----------+-----------+--------+------------+--------------+
结论:
活生生的例子:sqlfiddle
所以你有它。通过在STUDENTS
,EMPLOYEES
和USERS
表之间执行LEFT JOIN
,您将撤回两个表中的所有值以及所有默认用户值。从这里开始,您可以通过StudentId
或EmployeeId
来检查NULL
,以确定您正在使用哪种用户类型。
非常感谢你@ War10ck!这是一个很好的回应。我能够轻松探索基础知识。 :) – user6195207
用户表的限制是什么?如果StudentID和StaffID都是外键并且都是必需的,那么您将无法离开一个或另一个空 –
就是这样!约束条件是UserID是PK,StaffID和StudentID是FK。 – user6195207
我不希望StudentID和StaffID都是必需的,只需要一个或另一个。 – user6195207