mysql - 无法在 Mysql 中创建外部约束

Question

这是我尝试运行的脚本

CREATE TABLE IF NOT EXISTS `store` (
`store_id` INT NOT NULL AUTO_INCREMENT,
`store_name` VARCHAR(1024) NOT NULL,
`store_user` INT NOT NULL,
`store_address` INT NOT NULL,
`store_type` INT NOT NULL,
`created_date` DATETIME NOT NULL,
`updated_date` DATETIME NOT NULL,
PRIMARY KEY (`store_id`)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `store_address` (
`address_id` INT NOT NULL AUTO_INCREMENT,
`address_line_1` VARCHAR(1024) NOT NULL,
`address_line_2` VARCHAR(1024) NOT NULL,
`address_line_3` VARCHAR(1024) NULL,
`city` VARCHAR(45) NOT NULL,
`locality` VARCHAR(100) NOT NULL,
`pincode` CHAR(6) NOT NULL,
`latitude` DECIMAL(8,6) NULL,
`longitude` DECIMAL(9,6) NULL,
`state` VARCHAR(45) NOT NULL,
`created_date` DATETIME NOT NULL,
`updated_date` DATETIME NOT NULL,
PRIMARY KEY (`address_id`),
CONSTRAINT `FK_STR_STR_ADR`
FOREIGN KEY (`address_id`)
REFERENCES `store` (`store_address`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

我收到此错误 - 错误代码:1215。无法添加外键约束不知道 DDL 有什么问题

Answer 1

两个问题:
1.使用 unsigned int 作为主键(编辑:这是一个好主意，但不是问题)
2. store.store 地址必须是 store 表中的键，才能在 store_address 表的外键约束中使用它

 CREATE TABLE IF NOT EXISTS `store` (
 `store_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `store_name` VARCHAR(1024) NOT NULL,
 `store_user` INT NOT NULL,
 `store_address` int(10) unsigned NOT NULL DEFAULT 0,
 `store_type` INT NOT NULL,
 `created_date` DATETIME NOT NULL,
 `updated_date` DATETIME NOT NULL,
 PRIMARY KEY (`store_id`),
 KEY (`store_address`)
 ) ENGINE = InnoDB;

 CREATE TABLE IF NOT EXISTS `store_address` (
 `address_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `address_line_1` VARCHAR(1024) NOT NULL,
 `address_line_2` VARCHAR(1024) NOT NULL,
 `address_line_3` VARCHAR(1024) NULL,
 `city` VARCHAR(45) NOT NULL,
 `locality` VARCHAR(100) NOT NULL,
 `pincode` CHAR(6) NOT NULL,
 `latitude` DECIMAL(8,6) NULL,
 `longitude` DECIMAL(9,6) NULL,
 `state` VARCHAR(45) NOT NULL,
 `created_date` DATETIME NOT NULL,
 `updated_date` DATETIME NOT NULL,
 PRIMARY KEY (`address_id`),
 CONSTRAINT `FK_STR_STR_ADR` FOREIGN KEY (`address_id`) REFERENCES `store` (`store_address`)
 )ENGINE = InnoDB;