Cannot add Foreign key Constraint

Asked

Viewed 351 times

-2

Help with error in MySql

create table cliente(
    endereco varchar(256),
    codigo integer primary key auto_increment,
    telefone varchar (18),
    tipo varchar(20)
);

create table fisica(
    cpf varchar(20),
    foreign key(cpf) references cliente(tipo)
);
mysql

  • What error are you making when executing these commands?

  • 2

    Why do you have a table that has only one column, which is even a foreign key to another? It does not make sense that this table exists, including associating CPF to the type. In advance, for you to create a foreign key, the column in the other table needs to be a primary key. What exactly are you trying to do? It wasn’t clear in your question.

1 answer

-2

inserir a descrição da imagem aqui

  1. Every client is a person (Can be Physical or Legal);
  2. Every person has a type (Physical or Legal);

I created a modelling similar to yours but with some adjustments:

Below the script for creating objects:

CREATE TABLE IF NOT EXISTS `mydb`.`Cliente` (
  `codigo` INT(11) NOT NULL AUTO_INCREMENT,
  `numero_contrato` INT(11) NOT NULL,
  `dtcadastro` DATETIME NOT NULL,
  `dtrescisao` DATETIME NULL DEFAULT NULL,
  `pessoa` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`codigo`),
  INDEX `fk_cliente_01_idx` (`pessoa` ASC),
  CONSTRAINT `fk_cliente_01`
    FOREIGN KEY (`pessoa`)
    REFERENCES `mydb`.`Pessoa` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `mydb`.`Pessoa` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tipo` INT(10) UNSIGNED NOT NULL,
  `cpf_cnpj` VARCHAR(14) NULL DEFAULT NULL,
  `nome` VARCHAR(60) NULL DEFAULT NULL,
  `email` VARCHAR(150) NULL DEFAULT NULL,
  `cep` INT(11) NULL DEFAULT NULL,
  `logradouro` VARCHAR(100) NULL DEFAULT NULL,
  `numero` VARCHAR(20) NULL DEFAULT NULL,
  `complemento` VARCHAR(30) NULL DEFAULT NULL,
  `bairro` VARCHAR(70) NULL DEFAULT NULL,
  `cidade` VARCHAR(70) NULL DEFAULT NULL,
  `uf` VARCHAR(2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_pessoa_01_idx` (`tipo` ASC),
  CONSTRAINT `fk_pessoa_01`
    FOREIGN KEY (`tipo`)
    REFERENCES `mydb`.`TipoPessoa` (`codigo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `mydb`.`TipoPessoa` (
  `codigo` INT(10) UNSIGNED NOT NULL,
  `nome` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`codigo`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Just a tip research more about MER (Entity and Relationship Model), and try to use tools for modeling, in the case of Mysql you can use Workbench I do not know if you know: https://dev.mysql.com/downloads/workbench/ it will give you a better view of your project :)

I hope it helps you!

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.