Mysql error 1215: Cannot add foreign key constraint

Question

---

• How do I fix this mistake? I know one of the reasons for this is that the columns need to have the same size and specification. But I’ve tried everything and it’s not working.

---

CREATE DATABASE`sistema`/*!40100DEFAULT CHARACTER SET utf8*/;
use sistema;
drop database sistema;

CREATE TABLE`conta`(
    `numero`int(9)NOT NULL,
    `agencia`int(4)NOT NULL,
    `saldo`float NOT NULL,
    `limite`float NOT NULL,
    PRIMARY KEY(`numero`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE`correntista`(
    `cpf`varchar(11)NOT NULL,
    `nome`varchar(60)NOT NULL,
    `datanascimento`date NOT NULL,
    `sexo`varchar(1)NOT NULL,
    `idendereco`int(9)NOT NULL AUTO_INCREMENT,
    `numero`int(9)NOT NULL,
    PRIMARY KEY(`cpf`),
    KEY`idendereco`(`idendereco`),
    KEY`numero`(`numero`),
    CONSTRAINT`correntista_ibfk_1`FOREIGN KEY(`idendereco`)
        REFERENCES`endereco` (`idendereco`),
    CONSTRAINT`correntista_ibfk_2` FOREIGN KEY(`numero`)
        REFERENCES`conta`(`numero`)
)ENGINE=InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE`endereco`(
    `idendereco`int(9)NOT NULL AUTO_INCREMENT,
    `cep`varchar(8)NOT NULL,
    `logradouro`varchar(60)NOT NULL,
    `tipo`varchar(40)NOT NULL,
    `numero`int(9)NOT NULL,
    `bairro`varchar(40)NOT NULL,
    `cidade`varchar(60)NOT NULL,
    `uf`varchar(2)NOT NULL,
    PRIMARY KEY(`idendereco`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

Two things:

1 - You are trying to create a FOREIGN KEY referencing a table that does not yet exist.
The creation of the table correntista has to be done after the creation of the other two.

2 - Include a space between REFERENCES and the table name.

Try here

_{Note: Also remove the AUTO_INCREMENT country idendereco table correntista}

Answer 2

First create tables without FK At the end do an alter table

Example: ALTER TABLE pers_addresses ADD CONSTRAINT correntista_ibfk_11 FOREIGN KEY (idendereco) REFERENCES endereco (idendereco)