mySQL: What is preventing my foreign key constraint?

Question

I have tried everything I can think of but I am still having problems creating a table.

I have a user table with a primary key username

+---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | created_at | datetime | YES | | NULL | | | updated_at | datetime | YES | | NULL | | | username | varchar(50) | NO | PRI | NULL | | | administrator | tinyint(1) | YES | | NULL | | | fullname | text | YES | | NULL | | | description | text | YES | | NULL | | | password | varchar(60) | NO | | NULL | | +---------------+-------------+------+-----+---------+-------+

and I want to create a new table like this:

CREATE TABLE sessions ( created_at DATETIME, updated_at DATETIME, token VARCHAR(50) NOT NULL, username VARCHAR(50), PRIMARY KEY (token), FOREIGN KEY(username) REFERENCES users (username) );

but I get a nasty error:

ERROR 1215 (HY000): Cannot add foreign key constraint

I usually find this error is caused by a mismatch in the data type of the pk/fk pair but this time both are clearly varchar(50) so it looks like the problem is elsewhere.

I have also tried this just in case:

CREATE TABLE sessions ( created_at DATETIME, updated_at DATETIME, token VARCHAR(50) NOT NULL, username varchar(50) NOT NULL, #<- ***added not null*** PRIMARY KEY (token), FOREIGN KEY(username) REFERENCES users (username) );

mysql>SHOW ENGINE INNODB STATUS

LATEST FOREIGN KEY ERROR

2016-08-03 15:13:23 a46fcb70 Error in foreign key constraint of table savesdev/sessions: FOREIGN KEY(username) REFERENCES users (username)): 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. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.

It seems the error is raised under two circumstances:

1) When there is a mismatch (which I have ruled out)

column types in the table and the referenced table do not match for constraint.

2) When there is no suitable index on the referenced column

Cannot find an index in the referenced table where the referenced columns appear as the first columns

I think both of these are covered so what's the deal?

Can anyone spot my error?

Answer 1

Maybe your columns username have different charset can you try this :

ALTER TABLE sessions MODIFY username VARCHAR(50) CHARACTER SET utf8; 
ALTER TABLE users MODIFY username VARCHAR(50) CHARACTER SET utf8;

As suggested by @Graeme Stuart here is a link to see how we can check the charterer set of a database / table or a column : How do I see what character set a MySQL database / table / column is?