6

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?

Graeme Stuart
  • 5,837
  • 2
  • 26
  • 46
  • `show engine innodb status`. about 1/2 through the dump will be a "last foreign key error" section, with details. Most likely you have a field definition mismatch. FK relations have to be identical, e.g. `varchar(49) -> varchar(50)` isn't allowed, but `varchar(50)->varchar(50)` is. or you're on an older mysql which doesn't auto-create indexes on FK fields, and you haven't manually added a key on username. – Marc B Aug 03 '16 at 15:09
  • 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; – Anas EL KORCHI Aug 03 '16 at 15:20
  • That's a good point @elkorchianas, I will have a look.. – Graeme Stuart Aug 03 '16 at 15:23
  • 1
    Yes, correct! Could you add an answer? referencing this http://stackoverflow.com/a/4805510/1083707 or I can. – Graeme Stuart Aug 03 '16 at 15:34
  • It is similar to [this problem](http://stackoverflow.com/a/37649531) and perhaps my [Describe all Tables](http://stackoverflow.com/q/38693720) can help at times too – Drew Aug 03 '16 at 15:50

1 Answers1

8

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?

Community
  • 1
  • 1
Anas EL KORCHI
  • 2,008
  • 18
  • 25
  • 1
    Yes, the fields were both VARCHAR(50) but the existing table had a utf8_general_ci collation which made them incompatable. My actual solution was to create the new table with utf8_general_ci collation for the foreign key column. – Graeme Stuart Aug 03 '16 at 15:41