mysql - MySQL WorkBench 上的错误代码 1022

Question

我正在尝试创建这些表，但在创建表 cadastro_fornecedor 时收到错误 1022。我不知道从哪里开始解决这个问题，所以欢迎任何形式的帮助。

这是.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';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`administrador`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`administrador` (
  `id_administrador` INT NOT NULL,
  `nome` VARCHAR(45) NULL,
  `senha` VARCHAR(45) NULL,
  `CPF` INT NULL,
  `endereço` VARCHAR(45) NULL,
  `data_nascimento` DATE NULL,
  PRIMARY KEY (`id_administrador`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`cadastro_usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`cadastro_usuario` (
  `id_cadastro` INT NOT NULL AUTO_INCREMENT,
  `data_cadastro` DATETIME NOT NULL,
  `id_administrador` INT NULL,
  `usuario` INT NULL,
  PRIMARY KEY (`id_cadastro`),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  INDEX `matricula_idx` (`usuario` ASC),
  CONSTRAINT `id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `matricula`
    FOREIGN KEY (`usuario`)
    REFERENCES `mydb`.`usuario_cadastrado` (`matricula`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`usuario_cadastrado`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`usuario_cadastrado` (
  `matricula` INT NOT NULL,
  `nome` VARCHAR(45) NULL,
  `endereco` VARCHAR(45) NULL,
  `senha` VARCHAR(45) NULL,
  `nacionalidade` VARCHAR(45) NULL DEFAULT 'brasileiro',
  `naturalidade` VARCHAR(45) NULL,
  `sexo` CHAR NULL,
  `data_nascimento` DATE NULL,
  `CPF` INT NULL,
  `nome_pai` VARCHAR(45) NULL,
  `nome_mae` VARCHAR(45) NULL,
  `telefone_celular` VARCHAR(15) NULL,
  `telefone_fixo` VARCHAR(15) NULL,
  `id_cadastro` INT NULL,
  PRIMARY KEY (`matricula`),
  INDEX `id_cadastro_idx` (`id_cadastro` ASC),
  CONSTRAINT `id_cadastro`
    FOREIGN KEY (`id_cadastro`)
    REFERENCES `mydb`.`cadastro_usuario` (`id_cadastro`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`obra`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`obra` (
  `ISBN` VARCHAR(20) NOT NULL,
  `titulo` VARCHAR(45) NULL,
  `autor` VARCHAR(45) NULL,
  `ano` YEAR NULL,
  `editora` VARCHAR(45) NULL,
  `categoria` VARCHAR(45) NULL,
  `assunto` VARCHAR(45) NULL,
  `descricao` VARCHAR(45) NULL,
  PRIMARY KEY (`ISBN`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`fornecedores`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`fornecedores` (
  `CNPJ` VARCHAR(45) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `endereco` VARCHAR(45) NULL,
  `telefone` VARCHAR(45) NULL,
  PRIMARY KEY (`CNPJ`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`exemplar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`exemplar` (
  `codigo_exemplar` INT NOT NULL,
  `data_aquisicao` DATE NULL,
  `edição` INT NULL,
  `volume` INT NULL,
  `paginas` INT NULL,
  `ISBN` VARCHAR(20) NULL,
  `CNPJ` VARCHAR(45) NULL,
  PRIMARY KEY (`codigo_exemplar`),
  INDEX `ISBN_idx` (`ISBN` ASC),
  INDEX `CNPJ_idx` (`CNPJ` ASC),
  CONSTRAINT `ISBN`
    FOREIGN KEY (`ISBN`)
    REFERENCES `mydb`.`obra` (`ISBN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `CNPJ`
    FOREIGN KEY (`CNPJ`)
    REFERENCES `mydb`.`fornecedores` (`CNPJ`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`cadastro_fornecedor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`cadastro_fornecedor` (
  `id_cadastro` INT NOT NULL AUTO_INCREMENT,
  `data` DATETIME NULL,
  `id_administrador` INT NULL,
  `CNPJ` VARCHAR(45) NULL,
  PRIMARY KEY (`id_cadastro`),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  INDEX `CNPJ_idx` (`CNPJ` ASC),
  CONSTRAINT `id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `CNPJ`
    FOREIGN KEY (`CNPJ`)
    REFERENCES `mydb`.`fornecedores` (`CNPJ`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`emprestimo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`emprestimo` (
  `id_emprestimo` INT NOT NULL AUTO_INCREMENT,
  `data_emprestimo` DATE NULL,
  `data_devolucao_prevista` DATE NULL,
  `matricula` INT NULL,
  `id_administrador` INT NULL,
  `codigo_exemplar` INT NULL,
  PRIMARY KEY (`id_emprestimo`),
  INDEX `matricula_idx` (`matricula` ASC),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  INDEX `codigo_exemplar_idx` (`codigo_exemplar` ASC),
  CONSTRAINT `matricula`
    FOREIGN KEY (`matricula`)
    REFERENCES `mydb`.`usuario_cadastrado` (`matricula`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `codigo_exemplar`
    FOREIGN KEY (`codigo_exemplar`)
    REFERENCES `mydb`.`exemplar` (`codigo_exemplar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`devolucao`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`devolucao` (
  `id_devolucao` INT NOT NULL,
  `data_devolucao` DATE NULL,
  `multa` INT NULL,
  `matricula` INT NULL,
  `id_administrador` INT NULL,
  PRIMARY KEY (`id_devolucao`),
  INDEX `matricula_idx` (`matricula` ASC),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  CONSTRAINT `matricula`
    FOREIGN KEY (`matricula`)
    REFERENCES `mydb`.`usuario_cadastrado` (`matricula`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`exclusao`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`exclusao` (
  `id_exclusao` INT NOT NULL AUTO_INCREMENT,
  `data_exclusao` DATE NULL,
  `id_administrador` INT NULL,
  `codigo_exemplar` INT NULL,
  PRIMARY KEY (`id_exclusao`),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  INDEX `codigo_exemplar_idx` (`codigo_exemplar` ASC),
  CONSTRAINT `id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `codigo_exemplar`
    FOREIGN KEY (`codigo_exemplar`)
    REFERENCES `mydb`.`exemplar` (`codigo_exemplar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`registro`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`registro` (
  `id_registro` INT NOT NULL AUTO_INCREMENT,
  `data_registro` DATE NULL,
  `id_administrador` INT NULL,
  `codigo_exemplar` INT NULL,
  PRIMARY KEY (`id_registro`),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  INDEX `codigo_exemplar_idx` (`codigo_exemplar` ASC),
  CONSTRAINT `id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `codigo_exemplar`
    FOREIGN KEY (`codigo_exemplar`)
    REFERENCES `mydb`.`exemplar` (`codigo_exemplar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`reserva`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`reserva` (
  `id_reserva` INT NOT NULL,
  `data_reserva` DATE NULL,
  `matricula` INT NULL,
  `ISBN` VARCHAR(20) NULL,
  PRIMARY KEY (`id_reserva`),
  INDEX `matricula_idx` (`matricula` ASC),
  INDEX `ISBN_idx` (`ISBN` ASC),
  CONSTRAINT `matricula`
    FOREIGN KEY (`matricula`)
    REFERENCES `mydb`.`usuario_cadastrado` (`matricula`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ISBN`
    FOREIGN KEY (`ISBN`)
    REFERENCES `mydb`.`obra` (`ISBN`)
    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;

我感谢任何形式的帮助。谢谢。

Answer 1

您正在重用现有的约束名称(id_administrador 已在表 cadastro_usuario 中用作约束名称，并且 CNPJ 在表 exemplar 中使用)，您必须为约束指定唯一的名称(此处我将它们重命名为 cf_id_administrador)/CF_CNPJ);

-- -----------------------------------------------------
-- Table `mydb`.`cadastro_fornecedor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`cadastro_fornecedor` (
  `id_cadastro` INT NOT NULL AUTO_INCREMENT,
  `data` DATETIME NULL,
  `id_administrador` INT NULL,
  `CNPJ` VARCHAR(45) NULL,
  PRIMARY KEY (`id_cadastro`),
  INDEX `id_administrador_idx` (`id_administrador` ASC),
  INDEX `CNPJ_idx` (`CNPJ` ASC),
  CONSTRAINT `cf_id_administrador`
    FOREIGN KEY (`id_administrador`)
    REFERENCES `mydb`.`administrador` (`id_administrador`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `CF_CNPJ`
    FOREIGN KEY (`CNPJ`)
    REFERENCES `mydb`.`fornecedores` (`CNPJ`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;