1 – E: Restricción FOREIGN KEY

Las foreign key o clave foránea es de las cosas que más juego da a la hora de hacer consultas. Esto es porque relacionan directamente elementos de diferentes tablas. Esta restricción sin duda es la que más ayuda al ahora de mantener la integridad de los datos y asegurarnos de que no queda ni basura cuando borramos datos ni tampoco datos “perdidos” por la base de datos.

Un ejemplo: En la tabla paquetes usada anteriormente queremos saber a qué ciudad se envían (antes no sé como llegarían los paquetes). Tenemos dos opciones (una buena y una mala). La mala seria insertar una nueva columna de tipo varchar que ponga “Palencia”, “Valladolid” o la ciudad que sea. Esto es un claro ejemplo de redundancia ya que la palaba “Palencia” se repetirá bastantes veces (sí, es una ciudad pequeña pero algún paquete llegará ¿no?). Además sería muy poco fiable hacer búsquedas para saber que paquetes han mandando a una ciudad ya que un puede escribir “Palencia” otro “palencia” o cualquier cosa vete tu a saber (el usuario es imprevisible) además del curro que le mandamos hacer la base de datos. Recordemos que buscar por palabras es lo mas costoso así que cuanto más lo evitemos más rápido funcionará la base de datos. Para estas cosas se inventaron las claves foráneas que en este caso consistiría básicamente en tener una tabla solo para las ciudades y otra para los paquetes y estas dos enlazadas.

Veamos la diferencia con un dibujito

Aquí se esta suponiendo que se han introducido los datos correctamente (en un caso real pueden comterse errores) y observamos como la palabra Palencia aparece 4 veces, Valladolid 3 y Madrid 1. Imagina que tenemos miles de paquetes. Y por si fuera poco el espacio que ocupa pues el tiempo que tarda en hacer búsquedas en muchísimo más, pero mucho mucho. Esta tabla no estaría ni en la segunda forma normal

Lo ideal es sería esto:

Las principales ventajas son:

• Ahorramos espacio en la base de datos
• Consultas infinitamente más rápidas
• Si queremos cambiar el nombre de una ciudad lo hacemos 1 vez y no tantas como paquetes por esa ciudad hayan pasado
• Tener la certeza de que “Palencia” es Palencia y no “palencia” o “planecia” o cualquier fallo a la hora de insertar datos, que son cosas que pasan.
• Nunca habrá una paquete con destino una ciudad “desconocida”

En una palabra INTEGRIDAD de los datos.

Por cierto, buenas descripciones de los paquetes eh?

Lo ideal es evitar que se repitan datos a toda costa. Cuanto mas “segmentados” esten los datos más libertad tendremos para hacer consultas y menos redundancia de datos. Este caso es muy evidente pero hay otros en los que no y te das cuenta cuando ya tienes la base de datos hecha y con datos metidos y te toca rehacer todo (si quieres) por eso es recomendable pensar muy bien el esquema de la base de datos antes de escribir una sola línea SQL.

En MySQL hay diferentes tipos de tablas, la predeterminada es MyISAM pero para poder usar foreign key necesitamos usar InnoDB. Crear la tabla con foreign key (fk) liosillo así que lo que yo hago es crear la tabla y luego añadir la restricción. En el caso de los paquetes sería:

CREATE TABLE ciudades (
id_ciudad INT,
ciudad VARCHAR (25),
CONSTRAINT ciudades_pk PRIMARY KEY (id_ciudad)
)ENGINE=InnoDB;

CREATE TABLE paquetes (
codigo_paquete CHAR(5),
descripcion VARCHAR(50),
id_ciudad INT,
CONSTRAINT paquetes_pk PRIMARY KEY (codigo_paquete)
)ENGINE=InnoDB;
–Decimos que el id_ciudad de paquetes tiene que existir en ciudades
ALTER TABLE paquetes ADD CONSTRAINT paquetes_ciudades_fk FOREIGN KEY (id_ciudad) REFERENCES ciudades (id_ciudad);

Es importante que cuando creemos la restricción existan ambas tablas y que si hay datos, nos aseguremos de que esten bien porque si no dará error. Con estar bien me refiero a que si en un paquete tenemos como id_ciudad el 6 tiene que haber en ciudades una ciudad con el id_ciudad 6. Tampoco nos dejaria borrar la tabla ciudades hasta que eliminemos la restricción o borremos la tabla paquetes (y todas la tablas que hagan referencia a ésta).

Ahora por ejemplo podemos hacer algo más complejo y donde se aprecian mejor los beneficios de las foreign key. Vamos a hacer seguimiento de todas las ciudades por las que pasa un paquete y suponiendo que no pasa dos veces por la misma para que se más sencillo (normalmente un paquete no pasa por la misma ciudad dos veces así que…)

Creamos una tabla que relacione ambas claves, las del paquete con las ciudades. Además también pondremos la fecha que suele ser útil. Aunque no lo dije antes pero el tipo de dato en una foreign key tiene que ser exactamente el mismo en ambos lados (evidentemente). También pueden formar foreign key varias columnas de una tabla.

ALTER TABLE tablaADD CONSTRAINT restriccion_fk FOREIGN KEY (campo1,campo2) REFERENCES tabla_referenciada (campo1,campo2);

Vayamos con nuestra tablita

CREATE TABLE ciudades_paquetes (
codigo_paquete CHAR(5),
id_ciudad INT,
fecha DATE,
CONSTRAINT ciudades_paquetes_pk PRIMARY KEY (codigo_paquete,id_ciudad)
)ENGINE=InnoDB;

ALTER TABLE ciudades_paquetes ADD CONSTRAINT ciudades_paquetes_ciudades_fk FOREIGN KEY (id_ciudad) REFERENCES ciudades (id_ciudad);

ALTER TABLE ciudades_paquetes ADD CONSTRAINT ciudades_paquetes_paquetes_fk FOREIGN KEY (codigo_paquete) REFERENCES paquetes(codigo_paquete) ON DELETE CASCADE;

Ahí he puesto un ON DELETE CASCADE. Eso quiere decir que si se borra el paquete, automáticamente se borrará la ruta de el paquete. Sin embargo, si intentamos borrar la ciudad no nos dejará y nos dira que no podemos borrar la ciudad porque hay datos que hacen referencia a ella. Esto es lo que le da potencia a las foreign key.

También tenemos ON DELETE SET NULL que lo que hace es poner valor nulo en vez de borrar, pero en este caso no nos dejaría ya que la foreign key es clave primaria y por definición una clave primaria no puede ser nula. Aquí ya se empiezan a mezclar conceptos, por eso es necesario pensar bien las restricciones antes de ponerte a defininarlas como un loco.

Y por último ON UPDATE CASCADE que lo que hace es que si cambiamos el id de una ciudad automáticamente se cambia en esta tabla en todas las filas donde se haga referencia a esa ciudad.

Bien, ahora tenemos los datos pero son “solo” un montón códigos y claves. En el próximo capítulo creare una “vista” para visualizar todas las rutas y poder procesarlo fácilmente con PHP, sin tener que ir “enlazando” a mano los arrays en php que para eso estan las foreign key.

Así como el capítulo anterior (el de los checks) no era muy fructífero, éste si es muy importante así que si tienes dudas lo mejor es que me preguntes.

Entradas relacionadas:

1. Conoce SQL – SELECT – Parte 2 En la primera parte sobre SELECT vimos básicamente como enlazar...
2. Conoce SQL – Restricciones – PRIMARY KEY 1 – C: Restricción PRIMARY KEY Algo que toda tabla...
3. Conoce SQL – Restricciones – CHECK 1 – D: Restricción CHECK Como siempre digo, esta restricción...
4. Conoce SQL – SELECT – Parte 1 Conoce SQL – DQL – Parte 1 Todo lo visto...
5. Conoce SQL – Restricciones – NOT NULL 1- B: Restricción NOT NULL Algo que también he notado...