外键约束-(错误1215).请协助:)

Question

我看了这里的其他问题，似乎找不到我想要的答案。我正在尝试制作一个SQL数据库，其中针对每个表的ID设置AUTO_INCREMENT。我已经将表的外部ID的数据类型与表的主键进行了匹配。错误出现在以下表格上(仅包含外键的表格(：NUMBERS、CUSTOMER、TRUNK、TRUNK_GROUP

在这些表格上收到的错误是：

错误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;

Answer 1

多个表中存在小问题。以下是一些更正：

切换到正确的数据库

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表尚未创建，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_OS_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上运行了这个，它运行得很好。