我正在为我所在地区的企业列表设计一个 sql 数据库。我正在使用 mySQL WorkBench 设计数据库，但是我遇到了一些问题，无法创建数据库中的一个表。

这就是数据库设计的样子

我遇到的问题是 BusinessHours，我发现它有点令人困惑，因为我想将它与 BusinessDirectory 的 Foregin 键关联起来(每个 businessDirectory 都有很多 BusinessHours)“一对多”，这样您就可以代表 7 天一周中的。希望这是有道理的。

这是使用mySQL workbench生成的sql

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 `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Members`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Members` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Members` (
  `idMembers` INT NOT NULL AUTO_INCREMENT ,
  `firstName` VARCHAR(45) NOT NULL ,
  `lastName` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idMembers`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BusinessDirectory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`BusinessDirectory` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`BusinessDirectory` (
  `idBusinessDirectory` INT NOT NULL ,
  `businessName` VARCHAR(45) NOT NULL ,
  `businessDescription` VARCHAR(1000) NULL ,
  `businessLogo` VARCHAR(45) NULL ,
  `idMembers` INT NULL ,
  `directoryCategory` VARCHAR(45) NULL ,
  PRIMARY KEY (`idBusinessDirectory`) ,
  INDEX `idMembers_idx` (`idMembers` ASC) ,
  CONSTRAINT `idMembers`
    FOREIGN KEY (`idMembers` )
    REFERENCES `mydb`.`Members` (`idMembers` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BusinessAddress`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`BusinessAddress` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`BusinessAddress` (
  `idBusinessDirectory` INT NOT NULL ,
  `addressNumber` VARCHAR(5) NULL ,
  `addressAreaName` VARCHAR(45) NULL ,
  `addressLat` FLOAT(10,6) NULL ,
  `addressLong` FLOAT(10,6) NULL ,
  PRIMARY KEY (`idBusinessDirectory`) ,
  CONSTRAINT `idBusinessDirectory`
    FOREIGN KEY (`idBusinessDirectory` )
    REFERENCES `mydb`.`BusinessDirectory` (`idBusinessDirectory` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BusinessHours`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`BusinessHours` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`BusinessHours` (
  `idBusinessDirectory` INT NOT NULL ,
  `dayOfWeek` INT NULL ,
  `openingTime` TIME NULL ,
  `closingTime` TIME NULL ,
  PRIMARY KEY (`idBusinessDirectory`) ,
  CONSTRAINT `idBusinessDirectory`
    FOREIGN KEY (`idBusinessDirectory` )
    REFERENCES `mydb`.`BusinessDirectory` (`idBusinessDirectory` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;


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

所以我希望有人能帮我弄清楚为什么我无法将 BusinessHours 表添加到我的数据库中。

这是两个屏幕截图，显示了我在 phpMyAdmin 中收到的错误消息

任何帮助将不胜感激

最终更新的sql；

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 `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Members`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Members` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Members` (
  `idMembers` INT NOT NULL AUTO_INCREMENT ,
  `firstName` VARCHAR(45) NOT NULL ,
  `lastName` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idMembers`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BusinessDirectory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`BusinessDirectory` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`BusinessDirectory` (
  `idBusinessDirectory` INT NOT NULL ,
  `businessName` VARCHAR(45) NOT NULL ,
  `businessDescription` VARCHAR(1000) NULL ,
  `businessLogo` VARCHAR(45) NULL ,
  `idMembers` INT NULL ,
  `directoryCategory` VARCHAR(45) NULL ,
  PRIMARY KEY (`idBusinessDirectory`) ,
  INDEX `idMembers_idx` (`idMembers` ASC) ,
  CONSTRAINT `idMembers`
    FOREIGN KEY (`idMembers` )
    REFERENCES `mydb`.`Members` (`idMembers` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BusinessAddress`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`BusinessAddress` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`BusinessAddress` (
  `idBusinessDirectory_BusinessAddress` INT NOT NULL ,
  `addressNumber` VARCHAR(5) NULL ,
  `addressAreaName` VARCHAR(45) NULL ,
  `addressLat` FLOAT(10,6) NULL ,
  `addressLong` FLOAT(10,6) NULL ,
  PRIMARY KEY (`idBusinessDirectory_BusinessAddress`) ,
  CONSTRAINT `idBusinessDirectory_BusinessAddress`
    FOREIGN KEY (`idBusinessDirectory_BusinessAddress` )
    REFERENCES `mydb`.`BusinessDirectory` (`idBusinessDirectory` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BusinessHours`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`BusinessHours` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`BusinessHours` (
  `idBusinessDirectory_BusinessHours` INT NOT NULL ,
  `dayOfWeek` INT NULL ,
  `openingTime` TIME NULL ,
  `closingTime` TIME NULL ,
  PRIMARY KEY (`idBusinessDirectory_BusinessHours`) ,
  CONSTRAINT `idBusinessDirectory_BusinessHours`
    FOREIGN KEY (`idBusinessDirectory_BusinessHours` )
    REFERENCES `mydb`.`BusinessDirectory` (`idBusinessDirectory` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;


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

这是 Foregin 键选项卡的样子

谢谢大家的帮助

最佳答案

外键的约束名称 idBusinessDirectory 在表 BusinessHours 和 BusinessAddress 中重复。

为约束赋予不同的名称。

已更新

遵循正确的 FK 命名约定，这样就不会弹出这样的错误，仅通过约束的名称我们就可以知道约束中涉及的表。

fk_[referencing table name]_[referenced table name]_[referencing field name]

因此在您的情况下，约束将是

编辑用这个更新你的代码

BusinessAddress Table

CONSTRAINT `fk_BusinessAddress_BusinessDirectory_idBusinessDirectory`
FOREIGN KEY (`idBusinessDirectory` )
REFERENCES `mydb`.`BusinessDirectory` (`idBusinessDirectory` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)


BusinessHours Table

CONSTRAINT `fk_BusinessHours_BusinessDirectory_idBusinessDirectory`
FOREIGN KEY (`idBusinessDirectory` )
REFERENCES `mydb`.`BusinessDirectory` (`idBusinessDirectory` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)

您所有的约束都存储在 INNODB 中的这个 INFORMATION_SCHEMA.KEY_COLUMN_USAGE

REFER

希望对您有所帮助。

关于mysql - 未创建数据库表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/15493662/