CODEWITHSUNDEEP
mysqlforeign-keys
Smith John
Smith John

Reputation: 1097

MYSQL Cannot add foreign key constraint?

I am trying add foreign key to my table, but it got wrong:

ERROR 1215 (HY000) at line 34: Cannot add foreign key constraint

I check the column type and they are the same type, I can't figure out why.

And I run SHOW ENGINE INNODB STATUS

I got this:

2016-07-18 10:55:15 7f476acb6700 Error in foreign key constraint of table DGUT/staffCourse:
FOREIGN KEY (teacher) REFERENCES staff (teacher) ON DELETE CASCADE
) engine=innodb default charset=utf8:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.

And here is my code:

CREATE TABLE staff(
    id int(11) NOT NULL AUTO_INCREMENT,
    department varchar(50) NOT NULL,
    teacher varchar(50) NOT NULL,
    gender varchar(50) NOT NULL,
    title varchar(50) NOT NULL,
    note1 varchar(50), 
    note2 varchar(50), 
    primary key (id)
) engine=innodb default charset=utf8;

CREATE TABLE staffCourse(
    id int(11) NOT NULL AUTO_INCREMENT,
    teacher varchar(50) NOT NULL,
    snum varchar(50) NOT NULL,
    course varchar(50),
    credit varchar(50),
    teachWay varchar(50),
    courseType varchar(50),
    classNum varchar(50),
    className varchar(50),
    stuNum varchar(50),
    week varchar(50),
    section varchar(50),
    location varchar(50),
    CONSTRAINT FOREIGN KEY (teacher) REFERENCES staff (teacher) ON DELETE CASCADE,
    primary key (id)
) engine=innodb default charset=utf8;

Any suggestions would be appreciated.

Upvotes: 1

Views: 1507

Answers (1)

Drew
Drew

Reputation: 24960

Technically this would get you over the hump. teacher has no key in the staff table. Below it does.

create schema aTestDb8282;
use aTestDb8282;

drop table if exists staff;
CREATE TABLE staff(
    id int(11) NOT NULL AUTO_INCREMENT,
    department varchar(50) NOT NULL,
    teacher varchar(50) NOT NULL,
    gender varchar(50) NOT NULL,
    title varchar(50) NOT NULL,
    note1 varchar(50), 
    note2 varchar(50), 
    primary key (id),
    key `k_my_t_keyname` (teacher)
) engine=innodb default charset=utf8;

CREATE TABLE staffCourse(
    id int(11) NOT NULL AUTO_INCREMENT,
    teacher varchar(50) NOT NULL,
    snum varchar(50) NOT NULL,
    course varchar(50),
    credit varchar(50),
    teachWay varchar(50),
    courseType varchar(50),
    classNum varchar(50),
    className varchar(50),
    stuNum varchar(50),
    week varchar(50),
    section varchar(50),
    location varchar(50),
    CONSTRAINT FOREIGN KEY (teacher) REFERENCES staff (teacher) ON DELETE CASCADE,
    primary key (id)
) engine=innodb default charset=utf8;

drop schema aTestDb8282; -- clean up. Poof that db is gone

From the manual page Using FOREIGN KEY Constraints:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

That said, I don't think this is a good design. I would have an int or the like for a teacherId. Maybe right back to your staff.id but I would need to understand what you are doing.

What you are doing is fuzzy. Sure, the index would point back to two or more staff members (if there were plural or even 1 of course) for a quick lookup. That is what it wants to do as an FK: to confirm.

But let's say there are more than 1, saying, yes, there is a teacher named Sally Watkins. But it should go back to an id, not a name that could repeat. It is bad design.

Upvotes: 2

Related Questions