MySQL:无法使用外键创建表

Question

我正试图为一个简单的应用程序学习MySQL。当我试图用外键创建一个表时，我得到了以下错误。

mysql> create table User_Activity (SLNO INT NOT NULL AUTO_INCREMENT, 
DATEOFTASK TIMESTAMP default NOW(), 
TASKNAME VARCHAR(30) NOT NULL, 
TASKACTION VARCHAR(30) NOT NULL, 
BACKUP VARCHAR(5) NOT NULL, 
TASKSTATUS VARCHAR(15) NOT NULL, 
HANDLEDBY VARCHAR(30) NOT NULL SET utf8 DEFAULT NULL ,
 PRIMARY KEY (SLNO), 
FOREIGN KEY (HANDLEDBY) REFERENCES User_Access(NAME));

而错误是(即使设置为utf8，默认为NULL，我也会出错)

错误1064 (42000)：您的SQL语法出现了错误；请检查与MySQL服务器版本相对应的手册，以便在第1行使用接近“SET utf8默认NULL、主键、外键(HANDLEDBY)引用U”的正确语法。

没有设置utf8默认NULL的错误：

mysql> create table User_Activity (SLNO INT NOT NULL AUTO_INCREMENT,
DATEOFTASK TIMESTAMP default NOW(), 
TASKNAME VARCHAR(30) NOT NULL,
TASKACTION VARCHAR(30) NOT NULL,
 BACKUP VARCHAR(5) NOT NULL,
TASKSTATUS VARCHAR(15) NOT NULL, 
HANDLEDBY VARCHAR(30), 
PRIMARY KEY (SLNO), FOREIGN KEY (HANDLEDBY) REFERENCES User_Access(NAME));

ERROR 1005 (HY000): Can't create table 'activity.User_Activity' (errno: 150)

User_Access表的描述是，

mysql> DESC User_Access;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| SLNO        | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME        | varchar(30) | NO   |     | NULL    |                |
| PASSWORD    | varchar(30) | NO   |     | NULL    |                |
| DESIGNATION | varchar(30) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)

编辑1：

mysql>创建表User_Activity (SLNO AUTO_INCREMENT，DATEOFTASK时间戳默认值NOW()，TASKNAME (30) NULL，TASKACTION VARCHAR(30) NULL，BACKUP (5) NULL，TASKSTATUS (15) NULL，HANDLEDBY (30)非空字符集utf8默认NULL，主键(SLNO)，外键(HANDLEDBY)引用User_Access(名称)； 错误1064 (42000)：您的SQL语法出现了错误；请检查与MySQL服务器版本相对应的手册，以获得正确的语法，以便在第1行mysql>中使用接近“字符集utf8默认NULL、主键(mysql>)、外键(HANDLEDBY) RE”的语法。

是的，User_Access表仅为Inno_DB，这是输出

mysql> SHOW CREATE TABLE User_Access;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                  |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| User_Access | CREATE TABLE `User_Access` (
  `SLNO` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) NOT NULL,
  `PASSWORD` varchar(30) NOT NULL,
  `DESIGNATION` varchar(30) NOT NULL,
  PRIMARY KEY (`SLNO`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

Answer 1

最后，我从#mysql的IRC人员那里找到了解决方案。我在这里是为了帮助别人。

他告诉我，无论我用什么作为主键，或者它应该有索引字段。因此，我喜欢将索引添加到表Use_Activity中的NAME字段。

mysql> alter table User_Access add key(NAME);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后第二个查询执行得很好。

mysql>创建表User_Activity (SLNO AUTO_INCREMENT，DATEOFTASK时间戳默认值NOW()，TASKNAME (30) NULL，TASKACTION VARCHAR(30) NULL，BACKUP (5) NULL，TASKSTATUS (15) NULL，HANDLEDBY (30)字符集latin1 NULL，主键(SLNO)，外键(HANDLEDBY)引用User_Access(NAME))；

Query OK, 0 rows affected (0.21 sec)

Answer 2

您必须使用CHARACTER SET而不仅仅是SET。

编辑:另一个重要的语法要求是CHARACTER SET utf8语法必须放在NOT NULL之前。

...
HANDLEDBY VARCHAR(30) CHARACTER SET utf8 NOT NULL, /* can't use NULL as default */
...

如果列的字符集与引用列User_acces(NAME)的字符集相同，则外键将工作。

还必须满足其他要求，例如，引用的列必须在键中，表必须是InnoDB等。请参阅此处的完整条件列表：https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Answer 3

外键用于InnoDB存储引擎，而不是MyISAM。

需要在NAME列(由外键引用的列)上定义索引(或键)。

外键列的数据类型必须与引用列的数据类型相同。

规范的外键模式是引用父表中的主键；但也可以引用任何索引列。(通常，我们希望它是父表中的唯一键，但MySQL将其扩展到任何索引，包括非惟一索引。)