Conoce SQL – Restricciones – FOREIGN KEY

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

paquetes1

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:

esquema2

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.

You may also like...

11 Responses

  1. Cristian dice:

    Genial explicacion!! Me ha servido de muchisisma ayuda, estoy haciendo un proyecto con una base de datos en mySql y tenia problemas con las FOREIGN KEY… Ahora entiendo mejor su uso.

    Gracias!

  2. Cristian dice:

    Por cierto, el capitulo de crear una vista para visualizar todas las rutas y procesar datos esta por aqui ya??

  3. Maxpowel dice:

    Me alegro que te ayude mi explicación jeje.
    En cuanto a lo de las vistas, últimamente he estado un poco vago pero sabiendo que a alguien le interesa me pongo ya mismo con ello.
    Para esta noche seguramente ya lo tenga, aunque quiza haga primero un capitulo sobre SELECT ya que una vista no es mas que un SELECT “almacenado” en forma de tabla. Bueno, ya iré explicando todo eso

  4. Cristian dice:

    Perfecto! ya te ire contando mis experiencias.

  5. Celestino dice:

    Perfecto, me ha explicado lo que no había visto en otros sitios, el problema que tenía era que tenía datos mal metidos, los borre y a partir de ese momento ya pude crear las foreign keys.
    Gracias

  6. Maxpowel dice:

    El problema de myqsl es que te lanza errores muy genericos que ayudan poco. Si tienes algun problema con foreign key o cosas relativas a innodb usa esta consulta

    show engine innodb status

    Te muestra bastante informacion sobre el estado de innodb y te dice exactamente el problema del por qué no se ha creado la foreign key, en el caso de dar error.

  7. johans dice:

    bueno quiero que me alludes a reso ¿Diseñar un programa que nos permita a través de una base de datos el ingreso de la información personal de un empleado y los pagos efectuados a este hasta el presente mes. Y saber la suma de todos los pagos efectuados a ese empleado. lber este problema

  8. anRoswell dice:

    Amigo muchas gracias por la explicacion, me ha servido muchisimo la verdad, muy pero muy claro las cosas…

  9. anRoswell dice:

    Amigo muchas gracias por la explicacion, me ha servido muchisimo la verdad, muy pero muy claro las cosas…

    Aunq tambn deberias colocar soluciones a posibles errores q podriamos tener, como por ejemplo si alguien tiene una tabla de tipo MyISAM, genera error, entre otras…

  10. Bill Gates dice:

    XQ pones el logo d q no a mi soporte gratis?, tan malo es?

  11. Sonrisa dice:

    Hola muy buen comentario exelente sigue asi
    yo tengo en dudas en bases de datos, creo que son un poco mas complejas que estas, me gustaria si sabes algun metodo de evitar injections sql pero que no sea con el tipico script que hay en internet si no algo un poco mas avanzado ya que ese script esta hecho con numeros y contraseña de 4 numeros, pd ojala que el injection sea con valores alfanumericos y no sea a traves de fuerza bruta te agradeceria bastante ya que de eso estoy realizando mi tesis

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *