CODEWITHSUNDEEP
mysqlforeign-keysforeign-key-relationshipcreate-table
David Rego
David Rego

Reputation: 163

Mysql errno 150 trying to create table with foreign key references

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!

Upvotes: 3

Views: 561

Answers (4)

Rahul
Rahul

Reputation: 5636

Thetype of the field in a foreign key must be the same as the type of the column they're referencing.I think this problem error because type of the field in a foreign key is different from the column they're referencing. Your foreign key field is uid and referencing table your field is username.Here is the problem.Try to use same type of field in both table like

 FOREIGN KEY (uid) REFERENCES databaseB.users(id)

Hope you understand and works for you.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

you cant make reference key with two different types

in databaseA is integer uid and databaseB is varchar

try this

  FOREIGN KEY (uid) REFERENCES databaseB.users(id)

Upvotes: 2

Joe Meyer
Joe Meyer

Reputation: 4405

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)

Upvotes: 4

Borniet
Borniet

Reputation: 3546

Are both databases of the same type (MyISAM, INNODB)? You cannot point a foreign key to a MyISAM DB. This error can also occur when you are mixing types for the field type (which is the same in this case, varchar(255)), or when the encodings of the fields are different. Another reason could be that you don't have access to the other database.

Upvotes: 1

Related Questions