MySQL不正确的外键 [英] MySQL Incorrect Foreign Key

问题描述

MySQL(mariadb Ver 15.1 Distrib 10.1.44-MariaDB，用于使用readline 5.2的debian-linux-gnu(x86_64))给了我这个错误: ERROR 1005 (HY000) at line 129: Can't create table `Houdini`.`stamp` (errno: 150 "Foreign key constraint is incorrectly formed")

MySQL (mariadb Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2) gave me this error: ERROR 1005 (HY000) at line 129: Can't create table `Houdini`.`stamp` (errno: 150 "Foreign key constraint is incorrectly formed")

尝试创建此表时:

DROP TABLE IF EXISTS stamp;
CREATE TABLE stamp (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  group_id SMALLINT NOT NULL,
  member BOOLEAN NOT NULL DEFAULT FALSE,
  rank SMALLINT NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY(id),
  CONSTRAINT stamp_ibfk_1 FOREIGN KEY (group_id) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
); 

正确的外键约束是什么?我认为group_id由于某些原因无法使用

What is the correct foreign key constraint for this? I presume group_id can't be used for some reason

推荐答案

可能是两个键之间的数据类型不同

could be the data type between the two key are not the same

DROP TABLE IF EXISTS stamp;
CREATE TABLE stamp (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  group_id INT NOT NULL,
  member BOOLEAN NOT NULL DEFAULT FALSE,
  rank SMALLINT NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY(id),
  CONSTRAINT stamp_ibfk_1 FOREIGN KEY (group_id) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
); 

如果将int用作id，则应将int用作group_id

if you use int for id you should use int for group_id

这篇关于MySQL不正确的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！