刚刚学习MySQL,遇到了一个问题,总共三张表,表具体情况如下: 员工表、班级表、学生表。
其中
班级表有一个名叫“ class_fk_employee”的外键引用了员工表的ID主键;
学生表有一个名叫“student_fk_employee”的外键引用了员工表的ID主键;员工表、班级表创建正常,学生表创建时出错,错误提示: 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 '
CONSTRAINT student_fk_employee
FOREIGN KEY (fk_eid) REFERENCE' at line 8三张表的信息如下所示-- 员工表
CREATE TABLE employee(
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(10) NOT NULL,
emp_login_name VARCHAR(20) NOT NULL,
emp_password VARCHAR(10) NOT NULL,
emp_sal DECIMAL NOT NULL,
emp_comm DECIMAL,
emp_entry_time DATE NOT NULL,
emp_birthday DATE,
fk_did INT(2),
CONSTRAINT emp_fk_dep
FOREIGN KEY (fk_did)
REFERENCES department(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 班级表
CREATE TABLE classes (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(10),
class_week VARCHAR(10) NOT NULL,
class_status VARCHAR(10),
fk_tid INT(4), -- 引用教师表主键
fk_eid INT(4), -- 引用员工表主键
fk_typeid INT(2), -- 引用课程类型表主键
CONSTRAINT class_fk_teacher
FOREIGN KEY (fk_tid) REFERENCES teacher(id),
CONSTRAINT class_fk_employee
FOREIGN KEY (fk_eid) REFERENCES employee(id),
CONSTRAINT class_fk_course_type
FOREIGN KEY (fk_typeid) REFERENCES course_type(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;--学生表
CREATE TABLE student (
stu_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(10) NOT NULL,
stu_login_name VARCHAR(15) NOT NULL,
stu_password VARCHAR(10) NOT NULL,
stu_phone VARCHAR(11),
stu_guardian VARCHAR(10),
fk_eid INT(2), --引用员工表主键
CONSTRAINT student_fk_employee
FOREIGN KEY (fk_eid) REFERENCES employee(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;谢谢!
其中
班级表有一个名叫“ class_fk_employee”的外键引用了员工表的ID主键;
学生表有一个名叫“student_fk_employee”的外键引用了员工表的ID主键;员工表、班级表创建正常,学生表创建时出错,错误提示: 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 '
CONSTRAINT student_fk_employee
FOREIGN KEY (fk_eid) REFERENCE' at line 8三张表的信息如下所示-- 员工表
CREATE TABLE employee(
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(10) NOT NULL,
emp_login_name VARCHAR(20) NOT NULL,
emp_password VARCHAR(10) NOT NULL,
emp_sal DECIMAL NOT NULL,
emp_comm DECIMAL,
emp_entry_time DATE NOT NULL,
emp_birthday DATE,
fk_did INT(2),
CONSTRAINT emp_fk_dep
FOREIGN KEY (fk_did)
REFERENCES department(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 班级表
CREATE TABLE classes (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(10),
class_week VARCHAR(10) NOT NULL,
class_status VARCHAR(10),
fk_tid INT(4), -- 引用教师表主键
fk_eid INT(4), -- 引用员工表主键
fk_typeid INT(2), -- 引用课程类型表主键
CONSTRAINT class_fk_teacher
FOREIGN KEY (fk_tid) REFERENCES teacher(id),
CONSTRAINT class_fk_employee
FOREIGN KEY (fk_eid) REFERENCES employee(id),
CONSTRAINT class_fk_course_type
FOREIGN KEY (fk_typeid) REFERENCES course_type(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;--学生表
CREATE TABLE student (
stu_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(10) NOT NULL,
stu_login_name VARCHAR(15) NOT NULL,
stu_password VARCHAR(10) NOT NULL,
stu_phone VARCHAR(11),
stu_guardian VARCHAR(10),
fk_eid INT(2), --引用员工表主键
CONSTRAINT student_fk_employee
FOREIGN KEY (fk_eid) REFERENCES employee(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;谢谢!
解决方案 »
- mysql同步error(Slave_IO_Running: Connecting)
- 3表联合查询,速度慢。
- 这SQL语句。。。能用索引优化吗
- slave_IO_running 现在为 no
- 一个sql写法问题,请教~~~
- 谁能告诉我如何成功删除一个MySQL数据库
- 请问mysql中是否有和access中在date段的Now()函数功能!
- select * from table01 where field01 in (select field01 from table02)为什么错了
- 询问show binlog events的语法问题!
- 根据数据返回一行数据做标题,求mysqil写法
- 消息 102,级别 15,状态 1,第 1 行 '-' 附近有语法错误。
- MySQL5.1启动失败,错误为1067,求助
尝试下下面的sql语句:CREATE TABLE student (
stu_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(10) NOT NULL,
stu_login_name VARCHAR(15) NOT NULL,
stu_password VARCHAR(10) NOT NULL,
stu_phone VARCHAR(11),
stu_guardian VARCHAR(10),
fk_eid INT(2), -- 引用员工表主键
CONSTRAINT student_fk_employee
FOREIGN KEY (fk_eid) REFERENCES employee(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
应该是有空格导致的,你在检查一下吧。我试了一下,这样就会报错:-- 班级表
CREATE TABLE classes1 (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(10),
class_week VARCHAR(10) NOT NULL,
class_status VARCHAR(10),
fk_tid INT(4), -- 引用教师表主键
fk_eid INT(4), --引用员工表主键
fk_typeid INT(2) -- 引用课程类型表主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;而这样就不会报错:-- 班级表
CREATE TABLE classes1 (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(10),
class_week VARCHAR(10) NOT NULL,
class_status VARCHAR(10),
fk_tid INT(4), -- 引用教师表主键
fk_eid INT(4), -- 引用员工表主键
fk_typeid INT(2) -- 引用课程类型表主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;