Create Foreign Key on customer table

[RoninMP]

Hi,

I'm trying to add a foreign key on the customer_entity table, but it doesn't work:

CREATE TABLE IF NOT EXISTS test (
	id INT(10) NOT NULL,
	customer_id INT(10) NOT NULL,
	PRIMARY KEY (id),
	KEY (customer_id),
	INDEX ix_customer_id (customer_id ASC),
	CONSTRAINT fk_customer_id FOREIGN KEY (customer_id)
		REFERENCES customer_entity(entity_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

Here's the error message:

QLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

What is wrong?

Thanks.

Regards

[MrKhoa99 · Accepted solution]

Hello,

I think the reason for this issue is that foreign key customer_id isn't correct. Because customer_entity(entity_id) is unsigned not null. So, we need to change it to:

customer_id INT(10) UNSIGNED NOT NULL,

If still not working, please add SET FOREIGN_KEY_CHECKS=0; at the top of our sql. 

[RoninMP]

The solution was the missing UNSIGNED key word.

Thanks for you help.