Can not add foreign key constraint

Question

Can not add foreign key constraint

Navigation

#1 by (-2 votes)

-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

asked by anonymous 09.10.2018 / 02:20

1 answer

Catch last id inserted in mysql with php [duplicate] Problem in code

score -2 · Answer 1

Everycustomerisaperson(canbephysicalorlegal);

Everypersonhasatype(PhysicalorLegal);

Icreatedamodelsimilartoyours,butwithsomeadjustments:

Belowthescriptforcreatingobjects:

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 hint of more research on MER (Entity Model and Relationship), and try to use tools for modeling, in the case of MySql you can use the Workbench do not know if you know: link it will give you a better view of your project:)

I hope this helps you!