mysql errno:150“外键约束不正确” - MariaDB [英] mysql errno: 150 “Foreign key constraint is incorrectly formed”- MariaDB
问题描述
CREATE TABLE`message_map`(
`message_from` varchar (15)NOT NULL $ b $ message_id varchar(15)NOT NULL $ b $ message_to varchar(15)NOT NULL $ b $ message_status bit(1) b)ENGINE = InnoDB DEFAULT CHARSET = latin1;
ALTER TABLE`message_map`
ADD PRIMARY KEY(`message_from`,`message_id`,`message_to`),
ADD KEY`FK_ij6tystusydqijqp8lgoigo1c`(`message_id`),
用户表
`PHONE_NUMBER` varchar(15) 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`);
我试过下面的查询来应用外键约束
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;
我得到以下错误:
错误1005:无法创建表
< shema>
。#sql-1964_4
(errno:150>外键约束不正确)
我的观察
$ b
列(message_from,message_to)和USER_ID的数据类型是相同的
。还有USER_ID是主键。
问题是
?
感谢您的回答
首先有一个代码中的错误数量使得测试有点麻烦。其次,请确保在两个表中使用相同的字符集,我将message_map更改为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;
我从外键定义中移除了演示。
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.
这篇关于mysql errno:150“外键约束不正确” - MariaDB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!