MySQL Workbench前向工程师错误1215:无法添加外键约束 [英] MySQL Workbench Forward Engineer Error 1215: Cannot add foreign key constraint

问题描述

当我执行此脚本以创建2个表时，CUSTOMER表中的STORE列引用了USERS表中的ID列(这两个列均为INT):

When I execute this script to create 2 tables, where the STORE column in the CUSTOMER table references the ID column in the USERS table (both columns are INT):

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `part_finder` DEFAULT CHARACTER SET utf8 ;
USE `part_finder` ;

-- -----------------------------------------------------
-- Table `part_finder`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_finder`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `account` MEDIUMINT(7) UNSIGNED NOT NULL COMMENT 'Account organisation user belongs to',
  `name` VARCHAR(32) NULL DEFAULT NULL,
  `passenc` VARCHAR(32) NULL DEFAULT NULL,
  `email` VARCHAR(55) NULL DEFAULT NULL,
  `rank` DECIMAL(1,0) NULL DEFAULT '0',
  `ip_reg` VARCHAR(15) NULL DEFAULT NULL,
  `ip_visit` VARCHAR(15) NULL DEFAULT NULL,
  `dtreg` INT(11) NOT NULL,
  `dtvisit` INT(11) NOT NULL,
  `visits` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `pass` VARCHAR(25) NULL DEFAULT NULL,
  `make_filter_on` TINYINT(1) NULL DEFAULT FALSE,
  `brand_filter_on` TINYINT(1) NULL DEFAULT FALSE,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3;


-- -----------------------------------------------------
-- Table `part_finder`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_finder`.`customer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `store` INT(10) NOT NULL,
  `name` VARCHAR(32) NULL,
  `address` VARCHAR(45) NULL,
  `address_2` VARCHAR(45) NULL,
  `city` VARCHAR(15) NULL,
  `state` TINYINT UNSIGNED NOT NULL,
  `zip` CHAR(5) NULL,
  `phone` VARCHAR(15) NULL,
  `website` VARCHAR(45) NULL,
  `email` VARCHAR(55) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_customer_users_idx` (`store` ASC),
  CONSTRAINT `fk_customer_users`
    FOREIGN KEY (`store`)
    REFERENCES `part_finder`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

我收到此错误:

ERROR: Error 1215: Cannot add foreign key constraint

-- -----------------------------------------------------
-- Table `part_finder`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_finder`.`customer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `store` INT(10) NOT NULL,
  `name` VARCHAR(32) NULL,
  `address` VARCHAR(45) NULL,
  `address_2` VARCHAR(45) NULL,
  `city` VARCHAR(15) NULL,
  `state` TINYINT UNSIGNED NOT NULL,
  `zip` CHAR(5) NULL,
  `phone` VARCHAR(15) NULL,
  `website` VARCHAR(45) NULL,
  `email` VARCHAR(55) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_customer_users_idx` (`store` ASC),
  CONSTRAINT `fk_customer_users`
    FOREIGN KEY (`store`)
    REFERENCES `part_finder`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 6 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

我正在使用InnoDB，已经检查了外键列的数据类型，并确保已在引用表中使用主键.

I'm using InnoDB, I've checked the data types of my foreign key columns and I've made sure I'm using a primary key in the referenced table.

有什么想法吗?

推荐答案

父表中的列也许是INT UNSIGNED?

两个表中的数据类型必须完全相同.

They need to be exactly the same data type in both tables.

外键约束

可能会导致外键约束错误的原因:

1. 您正在尝试引用目标表上不存在的键. 确保它是另一个表上的键(可以是primary or unique key)

1. You are trying to reference a nonexistent key on the target table. Make sure that it is a key on the other table (it can be a primary or unique key),

列的类型不相同(例外是列 引用表上的nullable).

The types of the columns are not the same (exception is the column on the referecing table can be nullable).

这篇关于MySQL Workbench前向工程师错误1215:无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！