Error in adding foreign key

Question

Error in adding foreign key

Navigation

#1 by (2 votes)

0

I'm 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 reported error 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

mysql chave-estrangeira

asked by anonymous 26.01.2017 / 01:17

1 answer

Error in parameter passing to a Shell Script Loop Javascript - 1 + input

score 2 · Accepted Answer

According to the foreign key section in the MySQL:

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

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

 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;