mysql errno: 150 “外键约束格式不正确"- MariaDB

MESSAGE_MAP 表

 创建表`message_map` (`message_from` varchar(15) NOT NULL,`message_id` varchar(15) NOT NULL,`message_to` varchar(15) NOT NULL,`message_status` 位 (1) 默认 NULL) 引擎=InnoDB 默认字符集=latin1；更改表`message_map`添加主键(`message_from`，`message_id`，`message_to`)，添加键 `FK_ij6tystusydqijqp8lgoigo1c` (`message_id`),

用户表

 创建表 `user` (`USER_ID` varchar(15) NOT NULL,`PASSWORD` varchar(15) 默认为空，`PHONE_NUMBER` varchar(15) 默认为空，`USER_NAME` varchar(15) 默认为空，) 引擎=InnoDB 默认字符集=utf8；更改表`用户`添加主键(`USER_ID`)，添加唯一键`USER_ID_UNIQUE`(`USER_ID`)；

<块引用>

我尝试下面的查询来应用外键约束

ALTER TABLE `demo`.`message_map`添加约束`FK_MSG_MAP_USER`外键(`message_from`)参考 `demo`.`user` (`USER_ID`)删除级联在更新级联时，添加约束`FK_MSG_MAP_USER_TO`外键(`message_to`)参考 `demo`.`user` (`USER_ID`)删除级联更新级联；

我收到以下错误

<块引用>

ERROR 1005: Can't create table <shema>.#sql-1964_4 (errno: 150 >"外键约束格式不正确")

我的观察

<块引用>

列的数据类型(message_from,message_to)和USER_ID是相同.而且 USER_ID 也是一个主键.

问题是

出了什么问题?

感谢您的回答

解决方案

首先，您的代码中存在许多错误，这使得测试起来有点麻烦.其次，确保在两个表中使用相同的字符集，我将 message_map 更改为 utf8:

DROP TABLE message_map;创建表 message_map (message_from varchar(15) 非空，message_id varchar(15) 非空，message_to varchar(15) 非空，消息状态位 (1) 默认 NULL) 引擎=InnoDB 默认字符集=utf8；ALTER TABLE message_map添加主键(message_from，message_id，message_to)，添加密钥 FK_ij6tystusydqijqp8lgoigo1c (message_id);ALTER TABLE message_map添加约束 FK_MSG_MAP_USER外键(message_from)REFERENCES 用户 (USER_ID)删除级联在更新级联时，添加约束 FK_MSG_MAP_USER_TO外键(message_to)REFERENCES 用户 (USER_ID)删除级联更新级联；

我从外键定义中删除了 demo.

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 <shema>.#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.