Why does MySQL not allow this foreign key?

Question

I am using MySQL 5 to try and create two tables. Here are the two tables:

DROP TABLE IF EXISTS  `users` ; 
CREATE TABLE IF NOT EXISTS  `users` (
  `username` VARCHAR(50) not null ,
  `password` VARCHAR(50) not null,      
  `enabled` boolean not null,
  `accountNonExpired` boolean not null,
  `accountNonLocked` boolean not null,
  `credentialsNonExpired` boolean not null, 
  PRIMARY KEY (`username`)
  ) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS  `authorities` ;
create table IF NOT EXISTS `authorities` (
`username` VARCHAR(50) not null ,
`authority` varchar(50) not null,
foreign key (`username`) references `users` (`username`),
unique index authorities_idx_1 (username, authority)
) engine = InnoDb;

When I try to execute this statement, the users table is created but then I get the error:

 Error Code: 1005 
 Can't create table 'dental.authorities' (errno: 150)

I am not seeing why this foreign key fails when the two referenced columns are identical. Is there

This builds successfully for me: http://sqlfiddle.com/#!2/1e9acf and looks fine. What MySQL version are you using? (as long as `users` is created first) — Michael Berkowski, Jul 12 '13 at 11:54
Why are you using a varchar foreign key and not an if? Use a varchar field as a primary key is a bad ideia — Lefsler, Jul 12 '13 at 11:55
did you check the comments of a similar question: http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150?rq=1 — Kaffee, Jul 12 '13 at 11:55
@demonofnight: There's nothing intrinsically "wrong" with varchar primary keys. Or with decimal, char, float, date, or timestamp primary keys. — Mike Sherrill 'Cat Recall', Jul 12 '13 at 12:04

score 1 · Answer 1 · answered Jul 12 '13 at 12:01

1

Depending on your server version and settings, you might need to add

DEFAULT CHARACTER SET = utf8

to the CREATE TABLE statement for "authorities". That will match the character set of the referenced table.

answered Jul 12 '13 at 12:01

Mike Sherrill 'Cat Recall'

91,602
17
122
185

score 1 · Accepted Answer · answered Jul 12 '13 at 12:04

1

Foreign keys require both keys to have the same character set. Add

DEFAULT CHARACTER SET = utf8;

to your second table CREATE instruction.

Edit: Oh boy looks like I'm late to the party.

answered Jul 12 '13 at 12:04

benfranke

84
4

Thank you benfranke, I thought only the column data type had to match. – sonoerin Jul 13 '13 at 01:31

score 0 · Answer 3 · answered Jul 12 '13 at 12:03

check out following points:

i think DEFAULT CHARACTER SET = utf8; not provided to second table

1. The two tables must be ENGINE=InnoDB. (can be others: ENGINE=MyISAM
    works too)
 2. The two tables must have the same charset.
 3. The PK column(s) in the parent table and the FK column(s) must be
    the same data type.
 4. The PK column(s) in the parent table and the FK column(s), if they
    have a define collation type, must have the same collation type;
 5. If there is data already in the foreign key table, the FK column
    value(s) must match values in the parent table PK columns.
 6. And the child table cannot be a temporary table.

Hope this helps.

"can be others: ENGINE=MyISAM works too" <-- plain wrong! You don't get an error, still MyISAM doesn't support foreign keys — fancyPants, Jul 12 '13 at 12:06

Why does MySQL not allow this foreign key?

3 Answers3