This gives error: Can’t create table when creating this table in Mysql using Foreign key

Question

create table clientes
(
    cpf varchar(15) primary key,
    nome varchar(50),
    rg varchar(10),
    dt_nascimento date,
    senha varchar(10), 
    rua varchar (20),
    numero varchar(6),
    bairro varchar(20),
    cidade varchar(20),
    cep varchar(15), 
    estado varchar(20), 
    telefone varchar(15),
    estadia int(25)
);

create table apartamento
(
    registro int primary key auto_increment,
    cod_cliente varchar(15),
    valor float (5.2),
    dias varchar(25) ,
    CONSTRAINT fk_impar_clientes FOREIGN KEY(cod_cliente) 
    REFERENCES clientes(cpf) ON DELETE CASCATE,
    CONSTRAINT fk_ FOREIGN KEY(dias)
    REFERENCES clientes(estadia) ON DELETE CASCATE
);

Answer 1

Your greater problem was creating two primary keys for the same table (clientes(cpf) and clientes(estadia)):

CONSTRAINT fk_impar_clientes FOREIGN KEY(cod_cliente)
REFERENCES clientes(cpf) ON DELETE CASCATE,
CONSTRAINT fk_ FOREIGN KEY(dias)
REFERENCES clientes(estadia) ON DELETE CASCATE

If you’re already going to have all customer data, why put two keys? Data will already be linked with only one key!

• It is recommended to create a column for each record, identified as id. Soon, you will have no problem if a customer is registered without a number.

• To foreign key should be of the same type and size of reference.

• To stay should not be part of the customer’s registration, since he can return to *hotel* for a new stay.

So let’s conclude with the code!

Table structure:

CREATE TABLE clientes (
    `id` INT(10) PRIMARY KEY AUTO_INCREMENT,
    `cpf` VARCHAR(15),
    `nome` VARCHAR(50),
    `rg` VARCHAR(10),
    `dt_nascimento` DATE,
    `senha` VARCHAR(10),
    `rua` VARCHAR (20),
    `numero` VARCHAR(6),
    `bairro` VARCHAR(20),
    `cidade` VARCHAR(20),
    `cep` VARCHAR(15),
    `estado` VARCHAR(20),
    `telefone` VARCHAR(15),
    `estadia` INT(25)
) ENGINE=InnoDB;

CREATE TABLE apartamento (
    `id` INT(10) primary key auto_increment,
    `idcliente` INT(10),
    `valor` float (5.2),
    `dias` INT(25)
) ENGINE=InnoDB;

Creation of foreign key:

ALTER TABLE `apartamento` ADD CONSTRAINT `fk_cliente` FOREIGN KEY(`idcliente`) REFERENCES `clientes`(`id`);

Insert some data for test purposes:

INSERT INTO `clientes` VALUES ( NULL, '111.111.111-11', 'Fulano', '2222222222', '2017-04-22', 'MiNhAsEnHa', 'AV BRASIL', '2247', 'Deodoro', 'RIO DE JANEIRO', '21615-338', 'RJ', '(21)1234-5678', 31 );
INSERT INTO `apartamento` VALUES ( NULL, 1, 1500, 45 );

Test entered data (#1):

mysql> SELECT * FROM `apartamento` JOIN `clientes` ON `clientes`.`id` = `apartamento`.`idcliente`;
+----+-----------+-------+---------+----+----------------+--------+------------+---------------+------------+-----------+--------+---------+----------------+-----------+--------+---------------+
| id | idcliente | valor | estadia | id | cpf            | nome   | rg         | dt_nascimento | senha      | rua       | numero | bairro  | cidade         | cep       | estado | telefone      |
+----+-----------+-------+---------+----+----------------+--------+------------+---------------+------------+-----------+--------+---------+----------------+-----------+--------+---------------+
|  1 |         1 |  1500 |      45 |  1 | 111.111.111-11 | Fulano | 2222222222 | 2017-04-22    | MiNhAsEnHa | AV BRASIL | 2247   | Deodoro | RIO DE JANEIRO | 21615-338 | RJ     | (21)1234-5678 |
+----+-----------+-------+---------+----+----------------+--------+------------+---------------+------------+-----------+--------+---------+----------------+-----------+--------+---------------+
1 row in set (0.00 sec)

Test entered data (#2):

mysql> SELECT `clientes`.`nome`, `apartamento`.`estadia`, `apartamento`.`valor` FROM `apartamento` JOIN `clientes` ON `clientes`.`id` = `apartamento`.`idcliente`;
+--------+---------+-------+
| nome   | estadia | valor |
+--------+---------+-------+
| Fulano |      45 |  1500 |
+--------+---------+-------+
1 row in set (0.00 sec)

My source is a good tip to learn Foreign keys in Mysql:

Youtube: Course in Video