MySQL中的外键:错误1005

I'm a bit confused about adding foreign keys in MySQL

我对在MySQL中添加外键有点困惑

What I'm trying to do is reference the Students primary key with:

我想做的是参考学生的主键:

CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students);

However, what I get is

然而，我得到的是

ERROR 1005 (HY000): Can't create table 'test_db.Enrolled' (errno: 150)

I searched around and found

我四处寻找，终于找到了

MySQL "ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)"

MySQL“错误1005 (HY000):不能创建表'foo”。# sql-12c_4(errno:150)”

However, my Students table already has a primary key so this should not be an issue:

然而，我的学生表已经有了一个主键，所以这应该不是一个问题:

| Students | CREATE TABLE Students (
  sid char(20) NOT NULL DEFAULT '',
  name char(20) DEFAULT NULL,
  login char(10) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  gpa float DEFAULT NULL,
  PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

I feel like I'm missing something fairly basic but I can't appear to spot it. Any advice?

我觉得我错过了一些很基本的东西，但我似乎找不到。任何建议吗?

2 个解决方案

#1

You should add referenced column name after referenced table REFERENCES Students(sid). So you have to change your code to below solution

您应该在引用表引用学生(sid)之后添加引用的列名。因此，您必须将代码更改为解决方案下面的代码

CREATE TABLE Enrolled
(
  sid CHAR(20), 
  cid CHAR(20), 
  grade CHAR(2), 
  PRIMARY KEY (sid, cid), 
  FOREIGN KEY (sid) REFERENCES Students(sid)
);

#2

What about making the referenced column explicit? Something like:

如何使引用的列显式?喜欢的东西:

CREATE TABLE Enrolled(
     sid CHAR(20), 
     cid CHAR(20), 
     grade CHAR(2), 

     PRIMARY KEY (sid, cid), 
     FOREIGN KEY (sid) REFERENCES Students (sid)
);

Look at FOREIGN KEY (sid) REFERENCES Students (sid), last line. Also note that if the foreign key is a single column you can specify it just after the column type:

看一下外键(sid)引用学生(sid)，最后一行。还要注意，如果外键是单个列，可以在列类型之后指定:

CREATE TABLE Enrolled(
    sid CHAR(20) REFERENCES Students (sid), 
    ...

#1