时间戳

首页 > 解决方案 > 外键约束 -（错误 1215）。请协助：）

mysql - 外键约束 -（错误 1215）。请协助：）

问题描述

我在这里查看了其他问题，似乎找不到我正在寻找的答案。我正在尝试创建一个针对每个表的 ID 设置 AUTO_INCREMENT 的 SQL 数据库。我已经匹配了从表的外部 ID 到表的主键的数据类型。错误发生在以下表上（仅具有外键的表）：NUMBERS、CUSTOMER、TRUNK、TRUNK_GROUP

这些表上收到的错误是：

ERROR 1215 (HY000): 无法添加外键约束

下面是使用的代码。想知道是否有人有任何建议？

CREATE DATABASE IF NOT EXISTS `NOAS_DATABASE` DEFAULT CHARACTER SET utf8 ;
USE `NOAS_DATABASE` ;

-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`IP_ADDRESSES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`IP_ADDRESSES` (
  `IP_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `START_IP_RANGE` LONGBLOB NULL,
  `END_IP_RANGE` LONGBLOB NULL,
  PRIMARY KEY (`IP_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`NO_RANGE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`NO_RANGE` (
  `RANGE_ID` INT(10) NOT NULL AUTO_INCREMENTL,
  `START_NO_RANGE` LONGBLOB NULL,
  `END_NO_RANGE` LONGBLOB NULL,
  PRIMARY KEY (`RANGE_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`NUMBERS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`NUMBERS` (
  `NUM_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `IP_ID` VARCHAR(20) NULL,
  `RANGE_ID` VARCHAR(20) NULL,
  `CALL_BARRING_STATUS` TEXT(10) NULL,
  `ANONYMOUS_CALL_REJECT` TEXT(20) NULL,
  `CALL_DIVERT` TEXT(20) NULL,
  `CALL_DIVERT_DEST_NO` LONGBLOB NULL,
  PRIMARY KEY (`NUM_ID`),
  INDEX `IP_ID_idx` (`IP_ID` ASC) VISIBLE,
  INDEX `RANGE_ID_idx` (`RANGE_ID` ASC) VISIBLE,
  CONSTRAINT `IP_ID`
    FOREIGN KEY (`IP_ID`)
    REFERENCES `NOAS_DATABASE`.`IP_ADDRESSES` (`IP_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `RANGE_ID`
    FOREIGN KEY (`RANGE_ID`)
    REFERENCES `NOAS_DATABASE`.`NO_RANGE` (`RANGE_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`SERVICE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`SERVICE` (
  `SERVICE_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `SERVICE_STATUS` VARCHAR(20) NULL,
  `DOMAIN_NAME` LONGBLOB NULL,
  PRIMARY KEY (`SERVICE_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`CUSTOMER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`CUSTOMER` (
  `CUSTOMER_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `CUST_NETWORK_SET` TEXT(20) NULL,
  `BILLING_ID` TEXT(20) NULL,
  `LOCATION` TEXT(20) NULL,
  `SERVICE_ID` VARCHAR(20) NULL,
  PRIMARY KEY (`CUSTOMER_ID`),
  INDEX `SERVICE_ID_idx` (`SERVICE_ID` ASC) VISIBLE,
  CONSTRAINT `SERVICE_ID`
    FOREIGN KEY (`SERVICE_ID`)
    REFERENCES `NOAS_DATABASE`.`SERVICE` (`SERVICE_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`NETWORK_SET`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`NETWORK_SET` (
  `NETWORK_SET_OSS_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `PRIORITY_NOAS` TEXT(20) NULL,
  `PRIORITY_SBC` TEXT(20) NULL,
  PRIMARY KEY (`NETWORK_SET_OSS_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`TRUNK_GROUP`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`TRUNK_GROUP` (
  `TRUNK_GROUP_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` VARCHAR(20) NULL,
  `TRUNK_ID` VARCHAR(20) NULL,
  PRIMARY KEY (`TRUNK_GROUP_ID`),
  INDEX `CUSTOMER_ID_idx` (`CUSTOMER_ID` ASC) VISIBLE,
  INDEX `TRUNK_ID_idx` (`TRUNK_ID` ASC) VISIBLE,
  CONSTRAINT `CUSTOMER_ID`
    FOREIGN KEY (`CUSTOMER_ID`)
    REFERENCES `NOAS_DATABASE`.`CUSTOMER` (`CUSTOMER_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `TRUNK_ID`
    FOREIGN KEY (`TRUNK_ID`)
    REFERENCES `NOAS_DATABASE`.`TRUNK` (`TRUNK_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `NOAS_DATABASE`.`TRUNK`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `NOAS_DATABASE`.`TRUNK` (
  `TRUNK_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `TRUNK_GROUP_ID` VARCHAR(20) NULL,
  `NETWORK_SET_OSS_ID` VARCHAR(20) NULL,
  `NUM_ID` VARCHAR(20) NULL,
  `TRUNK_SERVICE_STATUS` TEXT(20) NULL,
  `TRUNK_GROUP_PRIORITY` TEXT(20) NULL,
  `TRUNK_CAC_LIMIT` TEXT(20) NULL,
  `HANDOVER_FORMAT` TEXT(20) NULL,
  PRIMARY KEY (`TRUNK_ID`),
  INDEX `NETWORK_SET_OSS_ID_idx` (`NETWORK_SET_OSS_ID` ASC) VISIBLE,
  INDEX `NUM_ID_idx` (`NUM_ID` ASC) VISIBLE,
  INDEX `TRUNK_GROUP_ID_idx` (`TRUNK_GROUP_ID` ASC) VISIBLE,
  CONSTRAINT `NETWORK_SET_OSS_ID`
    FOREIGN KEY (`NETWORK_SET_OSS_ID`)
    REFERENCES `NOAS_DATABASE`.`NETWORK_SET` (`NETWORK_SET_OSS_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `NUM_ID`
    FOREIGN KEY (`NUM_ID`)
    REFERENCES `NOAS_DATABASE`.`NUMBERS` (`NUM_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `TRUNK_GROUP_ID`
    FOREIGN KEY (`TRUNK_GROUP_ID`)
    REFERENCES `NOAS_DATABASE`.`TRUNK_GROUP` (`TRUNK_GROUP_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

标签： mysql

解决方案

多个表中存在小问题。这里有一些更正：

切换到正确的数据库

USE `NOAS_DATABASE` ;

IP地址表就好了

CREATE TABLE IF NOT EXISTS `IP_ADDRESSES` (
  `IP_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `START_IP_RANGE` LONGBLOB NULL,
  `END_IP_RANGE` LONGBLOB NULL,
  PRIMARY KEY (`IP_ID`))
ENGINE = InnoDB;

无范围表

正如评论员 Dan 提到的，AUTO_INCREMENT 中有一个类型。下面是修正版。

CREATE TABLE IF NOT EXISTS `NO_RANGE` (
  `RANGE_ID` INT(10) NOT NULL AUTO_INCREMENT, -- fixed typo
  `START_NO_RANGE` LONGBLOB NULL,
  `END_NO_RANGE` LONGBLOB NULL,
  PRIMARY KEY (`RANGE_ID`))
ENGINE = InnoDB;

数字有数据类型问题

IP_ID 和 Range_ID 应为 INT。已更正如下。

CREATE TABLE IF NOT EXISTS `NUMBERS` (
  `NUM_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `IP_ID` INT(10) NULL,  -- fixed datatype
  `RANGE_ID` INT(10) NULL, -- fixed datatype
  `CALL_BARRING_STATUS` TEXT(10) NULL,
  `ANONYMOUS_CALL_REJECT` TEXT(20) NULL,
  `CALL_DIVERT` TEXT(20) NULL,
  `CALL_DIVERT_DEST_NO` LONGBLOB NULL,
  PRIMARY KEY (`NUM_ID`),
  INDEX `IP_ID_idx` (`IP_ID` ASC),
  INDEX `RANGE_ID_idx` (`RANGE_ID` ASC),
  CONSTRAINT `IP_ID`
    FOREIGN KEY (`IP_ID`)
    REFERENCES `IP_ADDRESSES` (`IP_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `RANGE_ID`
    FOREIGN KEY (`RANGE_ID`)
    REFERENCES `NO_RANGE` (`RANGE_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

服务台不错

CREATE TABLE IF NOT EXISTS `SERVICE` (
  `SERVICE_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `SERVICE_STATUS` VARCHAR(20) NULL,
  `DOMAIN_NAME` LONGBLOB NULL,
  PRIMARY KEY (`SERVICE_ID`))
ENGINE = InnoDB;

客户表有数据类型问题

Service_ID 应该是一个 INT。更正如下。

CREATE TABLE IF NOT EXISTS `CUSTOMER` (
  `CUSTOMER_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `CUST_NETWORK_SET` TEXT(20) NULL,
  `BILLING_ID` TEXT(20) NULL,
  `LOCATION` TEXT(20) NULL,
  `SERVICE_ID` INT(10) NULL,  -- fixed datatype
  PRIMARY KEY (`CUSTOMER_ID`),
  INDEX `SERVICE_ID_idx` (`SERVICE_ID` ASC) ,
  CONSTRAINT `SERVICE_ID`
    FOREIGN KEY (`SERVICE_ID`)
    REFERENCES `SERVICE` (`SERVICE_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

网络设置好

CREATE TABLE IF NOT EXISTS `NETWORK_SET` (
  `NETWORK_SET_OSS_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `PRIORITY_NOAS` TEXT(20) NULL,
  `PRIORITY_SBC` TEXT(20) NULL,
  PRIMARY KEY (`NETWORK_SET_OSS_ID`))
ENGINE = InnoDB;

中继线组引用了尚未创建的表

Trunk 表尚未创建，Trunk_Group 指的是它。删除了该引用。考虑从trunk_group 字段中删除trunk_id 的可能性。

CREATE TABLE IF NOT EXISTS `TRUNK_GROUP` (
  `TRUNK_GROUP_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` INT(10) NULL,
  `TRUNK_ID` INT(10) NULL, -- consider removing this field
  PRIMARY KEY (`TRUNK_GROUP_ID`),
  INDEX `CUSTOMER_ID_idx` (`CUSTOMER_ID` ASC) ,
  INDEX `TRUNK_ID_idx` (`TRUNK_ID` ASC) ,
  CONSTRAINT `CUSTOMER_ID`
    FOREIGN KEY (`CUSTOMER_ID`)
    REFERENCES `CUSTOMER` (`CUSTOMER_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION 
    -- removed trunk_id reference to trunk
    )
ENGINE = InnoDB;

中继表的数据类型已修复

Trunk_Group_ID 和 Network_Set_OSS_ID 已切换为 INT。

CREATE TABLE IF NOT EXISTS `TRUNK` (
  `TRUNK_ID` INT(10) NOT NULL AUTO_INCREMENT,
  `TRUNK_GROUP_ID` INT(10) NULL, -- fixed datatype
  `NETWORK_SET_OSS_ID` INT(10) NULL, -- fixed datatype
  `NUM_ID` INT(10) NULL,
  `TRUNK_SERVICE_STATUS` TEXT(20) NULL,
  `TRUNK_GROUP_PRIORITY` TEXT(20) NULL,
  `TRUNK_CAC_LIMIT` TEXT(20) NULL,
  `HANDOVER_FORMAT` TEXT(20) NULL,
  PRIMARY KEY (`TRUNK_ID`),
  INDEX `NETWORK_SET_OSS_ID_idx` (`NETWORK_SET_OSS_ID` ASC) ,
  INDEX `NUM_ID_idx` (`NUM_ID` ASC) ,
  INDEX `TRUNK_GROUP_ID_idx` (`TRUNK_GROUP_ID` ASC),
  CONSTRAINT `NETWORK_SET_OSS_ID`
    FOREIGN KEY (`NETWORK_SET_OSS_ID`)
    REFERENCES `NETWORK_SET` (`NETWORK_SET_OSS_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `NUM_ID`
    FOREIGN KEY (`NUM_ID`)
    REFERENCES `NUMBERS` (`NUM_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `TRUNK_GROUP_ID`
    FOREIGN KEY (`TRUNK_GROUP_ID`)
    REFERENCES `TRUNK_GROUP` (`TRUNK_GROUP_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

避免循环引用

中继组和中继是相互引用的。我建议你有 Trunk 的关系，这样 Trunk 就属于一个 Trunk 组，并保持不变。我还删除了visible关键字。

我在 MySQL 5.7 上运行它，它运行良好。

推荐阅读