MySQL - Error: 150 "Foreign key constraint is incorrectly formed")

Question

Got an odd problem I cant solve after browsing dozens of forum posts, and my local SQL Books.

I've got two tables, and want to add a foreign key to one of them. The foreign key and primary key share the same datatype and charset and yet I cannot add the Foreign Key at all.

addon_account

name	type	comments
id	int(11)	Primary Key
name	varchar(60)	Primary Key
label	varchar(255)
shared	int(11)

addon_account_data

name	type	comments
id	int(11)	Primary Key
account_name	varchar(60)	Primary Key
money	double
owner	varchar()

The query I ran:

ALTER TABLE `addon_account_data` ADD FOREIGN KEY (`account_name`) REFERENCES `addon_account`(`name`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Can't get it to work. Tosses out the same issue the entire time.

Answer 1

You are creating a foreign key on addon_account_data(account_name) that references addon_account(name). You have a composite primary the referred table : addon_account(id, name).

This is not allowed in MySQL, as explained in the documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

Possible solutions:

• add an additional column in the referring table: addon_account_data(account_id, account_name) and create a composite primary key to the corresponding columns in addon_account

• create an index on addon_account(name) (probably the simplest solution)

• change the order of the columns in the primary key of the referred table, like: addon_account(name, id) (you might want to first consider the impacts this may have in terms of performance)

Answer 2

I am not exactly a MySQL guy, but:

I believe the problem is that you are referencing only part of the primary key: Your table addon_account has a composite key PK(id, name).

So, to put your relationship to work, you will need to add 'account_id' as part of the foreign key as well:

ALTER TABLE addon_account_data ADD FOREIGN KEY (account_id, account_name) REFERENCES addon_account(id, name)

This thread deals with something similar.

I hope this helps.

EDITED

I have installed a MySQL server instance on my local machine... (MySQL 8).

I have run the script below, and it worked (giving warnings about integer display being a deprecated feature, so I would recommend ommitting it):

CREATE TABLE addon_account(
id INT(11) NOT NULL,
`name` VARCHAR(60) NOT NULL,
label VARCHAR(255),
shared INT(11),
CONSTRAINT pk_addon_account PRIMARY KEY(id, `name`));


CREATE TABLE addon_account_data (
id INT(11) NOT NULL,
account_name VARCHAR(60) NOT NULL,
account_id INT(11),
money DOUBLE,
`owner` VARCHAR(255),
CONSTRAINT pk_addon_account_data PRIMARY KEY(id, account_name),
CONSTRAINT fk_addon_account_account_data FOREIGN KEY(account_id, account_name)
    REFERENCES addon_account(id, `name`));

Could you try it and see if this works for you?

I am not that familiar with MySQL.

Answer 3

make sure that the 2 tables have the same collation like COLLATE='utf8_general_ci'