Home / Questions / 339989
Accepted
omaza1990
Asked: 2020-03-27 14:23:33 +0800 CST 2020-03-27 14:23:33 +0800 CST

MySQL - Cannot add foreign key constraint

  • 772

I have these two tables and a query on it.

Clients table:

CREATE TABLE clientes (
    email VARCHAR(60) NOT NULL, 
    nombre VARCHAR(20) NOT NULL,
    apellidos VARCHAR(50) NOT NULL,
    dni VARCHAR(9) PRIMARY KEY NOT NULL,
    telefono VARCHAR(9) NOT NULL,
    direccion VARCHAR(50) NOT NULL,
    ciudad VARCHAR(50) NOT NULL
);

INSERT INTO clientes (email, nombre, apellidos, dni, telefono, direccion, ciudad) VALUES 
("[email protected]", "PABLO", "GOMEZ SUAREZ", "13245764V", "654789222", "CALLE LA VERDE", "GAMA");

Postal table:

CREATE TABLE cpostales (
    cpostal VARCHAR(5) PRIMARY KEY NOT NULL,
    ciudad VARCHAR(50) NOT NULL,
    FOREIGN KEY (ciudad) REFERENCES clientes(ciudad)
);

INSERT INTO cpostales (cpostal, ciudad) VALUES 
("39740", "SANTOÑA"),
("39790", "GAMA");

Why does it tell me that it cannot add the foreign key?

enter image description here

As a final result I would like, for example, when making a query that shows me the postal code of the client's city. For example:

  • Query:

Selection of name, city, cpostal of all the clients that are of "Gama".

SELECT clientes.nombre, clientes.ciudad, cpostales.cpostal FROM clientes, cpostales WHERE clientes.ciudad = "GAMA" AND clientes.ciudad=cpostales.ciudad

Would this be the result of the query?

mysql

1 Answers

  1. Best Answer

    Indeed, as @AgustinG recommends, creating an index in clientes.ciudad, you solve it:

    ALTER TABLE clientes ADD INDEX(ciudad);

    For the query you want to perform, although yours with Cartesian product is valid, I recommend an internal product, which is much more efficient:

    SELECT nombre,ciudad,cpostal 
  FROM clientes JOIN cpostales USING(ciudad)
  WHERE clientes.ciudad='GAMA';

    Although, to find a client's zip code, don't search for their city, but for their email or name. If you're searching by city, search directly on cpostalesand you won't need to link two tables.

    And, finally, you will not be able to insert the record of SANTOÑAin cpostales, because you do not yet have clientesany record of that beautiful Cantabrian town.

    Relationship between foreign key and inner product

    Note that an inner product ( INNER JOIN) is not the same as a foreign key ( FK). In fact they are used in totally different contexts:

    • INNER JOIN: it is a temporary link with the intention of combining the information of two tables according to the criteria defined in theON
    • FK: is a restriction that prevents data from being recorded in a table if it does not previously exist in another. Which additionally requires that the field in the referenced table be indexed. Hence, it is necessary to create that index.

    If you simply need to join tables, you don't need the foreign key, and therefore you don't need to create the index.

    You may have thought this way because Access, when you have previously established a foreign key (which it calls a relationship), makes queries easier for you by assuming a INNER JOINbetween those two tables. But they are two different things.

    • 2