MySQL - 无法将外键添加到表中
我有一个使用 MySQL 2005 的数据库。我有两个表，Enrollment 和 AlertMsg。
注册的主键有两列：UnitCode 和StudentID。这两列都是另一个表的外键。
AlertMsg 的主键是三列：UnitCode、StudentID 和AlertNo。
当我尝试在 AlertMsg 表中使用 UnitCode 创建外键时，如下所示：
ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCode)
REFERENCES Enrolment(UnitCode)
我收到以下错误：
SQL Execution Error.
Executed SQL statement: ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCode)
REFERENCES Enrolment (UnitCode)
Error Source: .Net SqlClient Data Provider
Error Message: There are no primary or candidate keys in the referenced table 'Enrolment' that match the referencing column list in the foreign key 'FK_AlertMsg_UnitCo_571DF1D5'.
Could not create constraint. See previous errors.
经过一番搜索后，似乎这是因为 UnitCode 不是 Enrolment 的主键。但 Enrolment 的表定义似乎表明确实如此。我是 SQL 的新手，所以我假设如果表定义的最左边的列中有一个键，则意味着它是表的主键。
有人可以帮忙吗？提前致谢。
I have a database using MySQL 2005. I have two tables, Enrolment and AlertMsg.
The primary keys for enrolment are two columns, UnitCode and StudentID. Both these two columns are foreign keys to another table.
The primary keys for AlertMsg are three columns, UnitCode, StudentID and AlertNo.
When I try to make a foreign key with UnitCode in the AlertMsg table, like so:
ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCode)
REFERENCES Enrolment(UnitCode)
I get the following error:
SQL Execution Error.
Executed SQL statement: ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCode)
REFERENCES Enrolment (UnitCode)
Error Source: .Net SqlClient Data Provider
Error Message: There are no primary or candidate keys in the referenced table 'Enrolment' that match the referencing column list in the foreign key 'FK_AlertMsg_UnitCo_571DF1D5'.
Could not create constraint. See previous errors.
After some searching it seems that this is because UnitCode isn't a primary key of Enrolment. But Enrolment's table definition seems to show that it is. I'm a bit of a newbie at SQL, so I assume that if the far left column of the table definition has a key in it, it means its a primary key of the table.
Can anyone help? Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你说“......的主键”。实际上，它可能不是多个键，而是使用多个列创建的一个键。这些列一起在表中必须是唯一的，因此您不能仅引用外键中的列之一。
如果您希望此语句起作用
表
Enrolment中的UnitCode中的值需要是唯一的，并且您需要在该列上添加唯一约束。我想情况并非如此，因此您无法添加必要的唯一约束。你可能应该使用这个：
You say "the primary keys for ...". Well actually it is probably not multiple keys but one key that is create using multiple columns. Those columns together is what needs to be unique in your table and therefore you can not just reference one of the columns in a foreign key.
If you want this statement to work
The values in
UnitCodein tableEnrolmentneeds to be unique and you need to add a unique constraint on that column. I guess that is not the case so you can't add the necessary unique constraint.You should probably use this instead:
标准主键不必是表中最左边的索引列，这只是定义表的开发人员和 DBA 的常见习惯，将主键列放在前面。
在标准 SQL 中，外键必须引用：
PRIMARY KEY或UNIQUE KEY的列。我怀疑您实际上使用的是 Microsoft SQL Server 2005，而不是 MySQL。没有称为“MySQL 2005”的此类产品版本。
Standard primary keys don't have to be the leftmost indexed column in a table, it's just a common habit that developers and DBA's who define the table put the primary key column(s) first.
In standard SQL, a foreign key must reference:
PRIMARY KEYorUNIQUE KEYin the referenced table.I suspect you're really using Microsoft SQL Server 2005, not MySQL. There's no such product release known as "MySQL 2005".
您可以尝试：
或者，
哈。
You can try:
Or
Hth.