Why can't I add foreign key constraint?

Question

When I created the students table's foreign key then the question occurred.

cur.execute(

"""

CREATE TABLE teachers
(
 id int NOT NULL auto_increment  primary key comment 'ID',
 teacher_name varchar(255) comment '教师姓名',
 teacher_sex varchar(255) comment '教师性别',
 teacher_age varchar(255) comment '教师年龄',
 teacher_pay int comment '课时费',
 course_name varchar(255)  COMMENT '课程名称'
 )ENGINE=InnoDB ;

""")

cur.execute("ALTER TABLE teachers COMMENT = '教师表';")
cur.execute("ALTER TABLE teachers ADD INDEX for_index_name(course_name);")

cur.execute(
"""

CREATE TABLE courses
 (
  id int NOT NULL auto_increment  primary key comment 'ID',
  course_id int  COMMENT '课程编号',
  course_name varchar(255) COMMENT '课程名称',
  time varchar(255) comment '上课时间',
  UNIQUE (course_id),
  FOREIGN KEY (course_name) REFERENCES teachers(course_name) ON DELETE CASCADE
  )ENGINE=InnoDB;
""")

cur.execute("ALTER TABLE courses COMMENT = '课程表';")
cur.execute("ALTER TABLE courses ADD INDEX for_index_name(course_name);")

**cur.execute(

"""

CREATE TABLE students
(
id int NOT NULL auto_increment  primary key comment 'ID',
students_name varchar(255) comment '学生姓名',
students_sex varchar(255) comment '学生性别',
students_age varchar(255) comment '学生年龄',
teacher_name varchar(255) comment '教师姓名',
course_name varchar(255) COMMENT '课程名称',
KEY teachers (teacher_name),
constraint teachers FOREIGN KEY (teacher_name) references 
teachers(teacher_name) ON DELETE CASCADE,
KEY courses (course_name),
constraint courses FOREIGN KEY (course_name) references courses(course_name) 
)ENGINE=InnoDB;
""")**

cur.execute("ALTER TABLE students COMMENT = '学生表';")

Can you please explain in more detail? – Arihant Aug 23 '18 at 03:43 — Arihant, Aug 23 '18 at 03:43

AxelH · Accepted Answer · 2018-08-23T06:33:25.310

The problem comes from constraint teachers FOREIGN KEY (teacher_name) references teachers(teacher_name) ON DELETE CASCADE,

Since teacher_name is not a PRIMARY KEY and has no index. The DB refuse the FK. Just add an index on this column :

ALTER TABLE teachers ADD INDEX for_index_teacher_name(teacher_name);

Fiddle with the correction on line 15

But I suggest you start using the ID instead of the names. This won't required those INDEX and this is easier to update a teacher name if it is only in the table teacher.

So instead of teacher_name in courses, you would have ref_teacher int. And add a FOREIGN KEY on teacher(id). This is a primary key so this won't required any INDEX and a PRIMARY KEY should never be updated so is much more maintainable.

Like this :

CREATE TABLE students
(
id int NOT NULL auto_increment  primary key comment 'ID',
students_name varchar(255) comment '学生姓名',
students_sex varchar(255) comment '学生性别',
students_age varchar(255) comment '学生年龄',
ref_teacher int comment '教师姓名',
ref_course int COMMENT '课程名称',
constraint teachers FOREIGN KEY (ref_teacher) references teachers(id) ON DELETE CASCADE,
constraint courses FOREIGN KEY (ref_course) references courses(id)
)ENGINE=InnoDB;

Fiddle with ID used for FK

Why can't I add foreign key constraint?

1 Answers1