mysql errno150,mysql errno：150“外键约束不正确” - MariaDB

MESSAGE_MAP TABLE

CREATE TABLE `message_map` (

`message_from` varchar(15) NOT NULL,

`message_id` varchar(15) NOT NULL,

`message_to` varchar(15) NOT NULL,

`message_status` bit(1) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `message_map`

ADD PRIMARY KEY (`message_from`,`message_id`,`message_to`),

ADD KEY `FK_ij6tystusydqijqp8lgoigo1c` (`message_id`),

USER TABLE

CREATE TABLE `user` (

`USER_ID` varchar(15) NOT NULL,

`PASSWORD` varchar(15) DEFAULT NULL,

`PHONE_NUMBER` varchar(15) DEFAULT NULL,

`USER_NAME` varchar(15) DEFAULT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user`

ADD PRIMARY KEY (`USER_ID`),

ADD UNIQUE KEY `USER_ID_UNIQUE` (`USER_ID`);

I tried below query to apply Foreign Key Constraint

ALTER TABLE `demo`.`message_map`

ADD CONSTRAINT `FK_MSG_MAP_USER`

FOREIGN KEY (`message_from`)

REFERENCES `demo`.`user` (`USER_ID`)

ON DELETE CASCADE

ON UPDATE CASCADE,

ADD CONSTRAINT `FK_MSG_MAP_USER_TO`

FOREIGN KEY (`message_to`)

REFERENCES `demo`.`user` (`USER_ID`)

ON DELETE CASCADE

ON UPDATE CASCADE;

Im getting the following error

ERROR 1005: Can't create table .#sql-1964_4 (errno: 150 >"Foreign key constraint is incorrectly formed")

My Observation

The data type of the columns (message_from,message_to) and USER_ID are

the same. And also USER_ID is a primary key.

Question is

What went wrong?

Thank you for the answers

解决方案

First there are a number of errors in your code that makes it a bit cumbersome to test. Second, make sure you use the same charset in both tables, I changed message_map to utf8:

DROP TABLE message_map;

CREATE TABLE message_map (

message_from varchar(15) NOT NULL,

message_id varchar(15) NOT NULL,

message_to varchar(15) NOT NULL,

message_status bit(1) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE message_map

ADD PRIMARY KEY (message_from,message_id,message_to),

ADD KEY FK_ij6tystusydqijqp8lgoigo1c (message_id);

ALTER TABLE message_map

ADD CONSTRAINT FK_MSG_MAP_USER

FOREIGN KEY (message_from)

REFERENCES user (USER_ID)

ON DELETE CASCADE

ON UPDATE CASCADE,

ADD CONSTRAINT FK_MSG_MAP_USER_TO

FOREIGN KEY (message_to)

REFERENCES user (USER_ID)

ON DELETE CASCADE

ON UPDATE CASCADE;

I removed demo from the foreign key definition.