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));

错误是(即使没有 SET utf8 DEFAULT NULL 我也会收到错误)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET utf8 DEFAULT NULL , PRIMARY KEY (SLNO), FOREIGN KEY (HANDLEDBY) REFERENCES U' at line 1

没有 SET utf8 DEFAULT 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> 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 CHARACTER SET utf8 DEFAULT NULL , PRIMARY KEY (SLNO), FOREIGN KEY (HANDLEDBY) REFERENCES User_Access(NAME));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8 DEFAULT NULL , PRIMARY KEY (SLNO), FOREIGN KEY (HANDLEDBY) RE' at line 1 mysql>

是的，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

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

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

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

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