Error in adding foreign key

Question

I am having trouble creating a foreign key. I am doing the project in Mysql Workbench. The generated SQL script is as follows:

DROP TABLE IF EXISTS `comercio`.`cliente` ;

CREATE TABLE IF NOT EXISTS `comercio`.`cliente` (
    `cliente_id` INT(11) NOT NULL AUTO_INCREMENT,
    `cpf` VARCHAR(15) NOT NULL,
    `nome` VARCHAR(50) NOT NULL,
    `rg` VARCHAR(50) NOT NULL,
    `sexo` VARCHAR(2) NOT NULL,
    `nascimento` DATE NOT NULL,
    `telefone` VARCHAR(50) NOT NULL,
    `email` VARCHAR(50) NOT NULL,
     PRIMARY KEY (`cliente_id`, `cpf`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

DROP TABLE IF EXISTS `comercio`.`endereco` ;

CREATE TABLE IF NOT EXISTS `comercio`.`endereco` (
    `endereco_id` INT(11) NOT NULL AUTO_INCREMENT,
    `cidade` VARCHAR(50) NOT NULL,
    `estado` VARCHAR(50) NOT NULL,
    `rua` VARCHAR(50) NOT NULL,
    `bairro` VARCHAR(50) NOT NULL,
    `numero` VARCHAR(50) NOT NULL,
    `cep` VARCHAR(50) NOT NULL,
    `complemento` VARCHAR(50) NOT NULL,
    `cpf_cliente` VARCHAR(15) NOT NULL,
    PRIMARY KEY (`endereco_id`),
    INDEX `FK_CPF_CLIENTE_idx` (`cpf_cliente` ASC),
    CONSTRAINT `FK_CPF_CLIENTE`
        FOREIGN KEY (`cpf_cliente`)
        REFERENCES `comercio`.`cliente` (`cpf`)
        ON DELETE CASCADE
        ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

The error reported is as follows:

Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        CREATE TABLE IF NOT EXISTS `comercio`.`endereco` (
          `endereco_id` INT(11) NOT NULL AUTO_INCREMENT,
          `cidade` VARCHAR(50) NOT NULL,
          `estado` VARCHAR(50) NOT NULL,
          `rua` VARCHAR(50) NOT NULL,
          `bairro` VARCHAR(50) NOT NULL,
          `numero` VARCHAR(50) NOT NULL,
          `cep` VARCHAR(50) NOT NULL,
          `complemento` VARCHAR(50) NOT NULL,
          `cpf_cliente` VARCHAR(15) NOT NULL,
          PRIMARY KEY (`endereco_id`),
          INDEX `FK_CPF_CLIENTE_idx` (`cpf_cliente` ASC),
          CONSTRAINT `FK_CPF_CLIENTE`
            FOREIGN KEY (`cpf_cliente`)
            REFERENCES `comercio`.`cliente` (`cpf`)
            ON DELETE CASCADE
            ON UPDATE CASCADE)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = latin1

SQL script execution finished: statements: 9 succeeded, 1 failed

Answer 1

According to the section on foreign key in the Mysql documentation:

Innodb allows a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

That is, in your case, like the column cpf appears as the second column declared in your PRIMARY KEY, you should create another index for it, example:

 INDEX `CPF_idx` (`cpf` ASC)

Your entire script would look like this:

DROP TABLE IF EXISTS `cliente` ;

CREATE TABLE IF NOT EXISTS `cliente` (
    `cliente_id` INT(11) NOT NULL AUTO_INCREMENT,
    `cpf` VARCHAR(15) NOT NULL,
    `nome` VARCHAR(50) NOT NULL,
    `rg` VARCHAR(50) NOT NULL,
    `sexo` VARCHAR(2) NOT NULL,
    `nascimento` DATE NOT NULL,
    `telefone` VARCHAR(50) NOT NULL,
    `email` VARCHAR(50) NOT NULL,
     PRIMARY KEY (`cliente_id`, `cpf`),
     INDEX `CPF_idx` (`cpf` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

DROP TABLE IF EXISTS `endereco` ;

CREATE TABLE IF NOT EXISTS `endereco` (
    `endereco_id` INT(11) NOT NULL AUTO_INCREMENT,
    `cidade` VARCHAR(50) NOT NULL,
    `estado` VARCHAR(50) NOT NULL,
    `rua` VARCHAR(50) NOT NULL,
    `bairro` VARCHAR(50) NOT NULL,
    `numero` VARCHAR(50) NOT NULL,
    `cep` VARCHAR(50) NOT NULL,
    `complemento` VARCHAR(50) NOT NULL,
    `cpf_cliente` VARCHAR(15) NOT NULL,
    PRIMARY KEY (`endereco_id`),
    INDEX `FK_CPF_CLIENTE_idx` (`cpf_cliente` ASC),
    CONSTRAINT `FK_CPF_CLIENTE`
        FOREIGN KEY (`cpf_cliente`)
        REFERENCES `cliente` (`cpf`)
        ON DELETE CASCADE
        ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;