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
);
You are giving the error: Can not create table when creating this table in MySql using foreign key [closed]
1
asked by anonymous 21.04.2017 / 18:51
1 answer
0
Your biggest problem was to create two primary key 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 already have all the customer data, why put two keys ? The data will already be all linked with just one key!
-
We recommend creating a column for each record, identified as
id. So you will not have a problem if a client is registered without CPF .
The foreign key must be of the same type and size as the reference .
The stay should not be part of the customer's registration, since he can return to * hotel * for a new stay. / li>
So, let's conclude with the code!
Structure of tables:
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;
Foreign key creation:
ALTER TABLE 'apartamento' ADD CONSTRAINT 'fk_cliente' FOREIGN KEY('idcliente') REFERENCES 'clientes'('id');
Enter some data for testing:
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 the 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 the 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 for learning Foreign keys in MySQL :
22.04.2017 / 17:55