Mysql errno 150 trying to create table with foreign key references

Question

I'm trying to create a table in mysql with a foreign key reference, like this:

In database A:

CREATE TABLE replication (
  id varchar(255) NOT NULL PRIMARY KEY,
  uid varchar(255) NOT NULL,
  value int(11) NOT NULL,
  FOREIGN KEY (uid) REFERENCES databaseB.users(username)
);

In database B i have a table named users like this:

+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | varchar(255) | NO   |     | NULL    |       |
| username        | varchar(255) | NO   | PRI | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

When i try to create table replication, gives me the following error:

ERROR 1005 (HY000): Can't create table 'databaseA.replication' (errno: 150)

Any idea? Thanks in advance!

Answer 1

You need to add an index to uid or you will not be able to set it up to reference anything.

Also, typically in your DatabaseB you would have your users table have a Primary Key on ID and a unique index on username. Then you would set up a foreign key from DatabaseA.replication REFERENCES databaseB.users(id)