Una Foreign Key (en castellano: clave extranjera o clave foránea) es una columna (o varias columnas) de una tabla que se corresponden exactamente con una columna (o varias columnas) de otra tabla donde están definidas como Primary Key o como Unique
De esta forma se arma una relación padre/hijo, también conocida como maestro/detalle o como cabecera/detalle entre ambas tablas.
¿Para qué sirven las Foreign Keys?
Para asegurarnos que en la tabla hija solamente se graben valores que existen en la tabla padre.
Así, nunca tendríamos una venta que no se sabe a cual cliente se le vendió, o un producto que se vendió pero no se sabe el número de la Factura de venta, o un alumno a quien se le tomó un examen pero no se sabe cual examen se le tomó, o un cheque se emitió pero no se sabe el nombre del beneficiario de ese cheque, etc.
Si usamos Foreign Keys el mismo Firebird nos impedirá que grabemos una fila hija si no existe el padre de dicha fila. Por lo tanto, sería imposible que nos encontremos con errores como los descritos arriba.
Es una importantísima medida de seguridad y como tal es altamente recomendable que usemos Foreign Keys porque eso le dará mucha confiabilidad a nuestra Base de Datos.
¿Cómo se crea una Foreign Key?
Primero, la tabla padre debe existir y tener un identificador único (este identificador único puede ser una restricción Primary Key o una restricción Unique)
Segundo, a la tabla hija se le agrega una columna (o más de una columna) que correspondan al mismo tipo de datos que la Primary Key o la Unique de la tabla padre. Eso significa que si el identificador de la tabla padre es BIGINT, el identificador que se usa en la tabla hija para relacionarlo con la tabla padre también debe ser BIGINT. Si el identificador del padre es INTEGER, el identificador en la tabla hija usado para la relación también debe ser INTEGER, etc. Los nombres de los identificadores no importan, pero los tipos de datos sí, pues deben ser idénticos.
Ejemplo:
(Para facilitar la comprensión utilizaremos el programa EMS SQL Manager, pero cualquier otro programa administrador gráfico también servirá o podríamos hacer todo esto manualmente con el programa ISQL que viene incluido con el Firebird.)
Captura 1. Si haces clic en la imagen la verás más grande
En esta imagen se muestran dos columnas de la tabla CLIENTES (tiene muchas más columnas, pero no nos interesan ahora). Como se puede ver, el tipo de datos de la columna CLI_IDENTI (identificador del cliente) es de tipo BIGINT. Esta es la tabla que actuará como padre.
Captura 2. Si haces clic en la imagen la verás más grande
Esta tabla se llama MOVIMCAB y se usa para guardar en ella las cabeceras de los movimientos. Aquí, la columna donde se grabará el identificador del cliente también es de tipo BIGINT, por lo tanto podemos utilizarla sin problemas. Fíjate que esta tabla tiene dos identificadores: el que identifica a cada movimiento ocurrido y el que identifica a cada cliente.
Si hacemos clic en la pestaña «Constraints» y luego clic en la pestaña «Foreign Keys» y luego clic con el botón derecho, veremos este menú contextual:
Captura 3. Si haces clic en la imagen la verás más grande
haciendo clic allí veremos una ventana de nombre «Constraints» la cual nos permite definir a la nueva Foreign Key. Podemos elegir su nombre, la columna o columnas de la tabla hija, el nombre de la tabla padre y la o las columnas de la tabla padre, y las reglas que se aplicarán cuando se borre o se modifique una fila de la tabla padre.
Captura 4. Si haces clic en la imagen la verás más grande
Como puedes ver en la Captura 4., se relacionó la columna MVC_IDECLI con la columna CLI_IDENTI de la tabla CLIENTES. ¿Qué significa eso? que desde este momento en la columna MVC_IDECLI solamente se podrá ingresar un número que ya exista en la columna CLI_IDENTI.
¿Qué son las reglas de integridad?
Si miras en la parte inferior de la imagen de arriba verás que dice «On Delete Rule» y «On Update Rule«. ¿Qué significa eso?
Son las acciones que debe realizar el Firebird cuando se modifica (UPDATE) o se borra (DELETE) una fila de la tabla padre. Esas reglas de integridad son las siguientes:
NO ACTION
Es la opción por defecto. Impide que el identificador de la tabla padre sea cambiado o que la fila padre sea borrada. Si se intenta una de esas operaciones el Firebird se enojará.
Captura 5. Si haces clic en la imagen la verás más grande
«Foreign key references are present for the record» significa que hay al menos una fila en alguna tabla hija (porque una tabla padre puede tener muchas tablas hijas) con el mismo identificador que se quiso cambiar o borrar. Como la regla de integridad es NO ACTION, eso no está permitido.
ON UPDATE CASCADE
Si se cambia el identificador de la tabla padre también se cambia el identificador relacionado en la tabla hija. Cuidado con esto porque si la tabla hija tiene millones de filas entonces cambiar todos los identificadores puede llevar un buen tiempo. Recuerda que en este caso al cambiar el identificador de la fila padre, se cambian los identificadores de todas las filas hijas, en todas las tablas que tengan la regla de integridad ON UPDATE CASCADE. En general, no es bueno hacer algo así.
Captura 6. Si haces clic en la imagen la verás más grande
En la Captura 6. podemos ver los valores originales del identificador del cliente. En ambos casos el identificador del cliente es 1 y está todo ok, así debe ser. En la columna MVC_IDECLI de la tabla MOVIMCAB se introdujo el número 1 y como hay un cliente que tiene ese identificador la grabación se realizó sin problemas. Después se cambió el identificador en la tabla CLIENTES, se reemplazó el 1 por un 2 y el Firebird automáticamente cambió el valor de la columna MVC_IDECLI, como podemos ver en la imagen de abajo.
Captura 7. Si haces clic en la imagen la verás más grande
El cambio de identificador en la columna MVC_IDECLI fue automático. Luego de cambiar el identificador en CLI_IDENTI de 1 a 2 y ejecutar el COMMIT correspondiente, ese mismo número 2 apareció en la columna MVC_IDECLI.
ON DELETE CASCADE
Si se borra una fila de la tabla padre también se borran todas las filas relacionadas de la tabla hija. ¡¡¡Cuidado con esto!!! porque puede ser muy peligroso. En nuestro ejemplo, si borras al cliente también estarás borrando todas las ventas que se le hicieron a ese cliente. Hay que pensarlo muy bien antes de establecer como regla de integridad a ON DELETE CASCADE, porque un error de apreciación puede ser trágico.
SET NULL
El identificador en la tabla hija es puesto a NULL. Eso implica que la fila hija se convierte en huérfana ya que no tiene padre. Evidentemente esta regla de integridad no puede ser aplicada si la columna de la tabla hija no admite NULL.
SET DEFAULT
El identificador de la tabla hija es puesto a su valor por defecto. Cuando definimos una columna podemos establecer que tendrá un valor por defecto cuando ningún valor le es asignado explícitamente. Ese será el valor que tendrá el identificador de la tabla hija cuando el identificador de la tabla padre sea cambiado o la fila padre sea borrada. Algunos puntos que se deben recordar son:
- El valor por defecto es el que estaba siendo usado cuando la restricción Foreign Key fue definida. Si más tarde se cambió ese valor por defecto no importa, el valor original será el utilizado. Cuidado con esto.
- Si ningún valor por defecto fue declarado para la columna entonces su valor por defecto es NULL. En ese caso, es lo mismo que usar la regla de integridad SET NULL
- Si el valor por defecto no existe en el identificador de la tabla padre, ocurrirá un error
¿Cómo se puede ver el comando SQL que crea a la Foreign Key?
En la misma ventana llamada «Constraints«, a la izquierda, hay una opción rotulada «DDL«. Haciendo click en ella nos muestra el comando SQL correspondiente a esa Foreign Key, como puedes ver en la imagen de abajo:
Captura 8. Si haces clic en la imagen la verás más grande
Nos dice que se modificó la tabla MOVIMCAB para agregarle una restricción llamada FK_MOVIMCAB, la cual es una Foreign Key que relaciona a la columna MVC_IDECLI de la tabla MOVIMCAB con la columna CLI_IDENTI de la tabla CLIENTES.
Y aquí está la forma en que se crea una tabla y sus restricciones si no usas un administrador gráfico (en este artículo se usó EMS SQL Manager, hay varios más, para ser productivo es aconsejable que uses alguno porque ahorrarás muchísimo tiempo). Esto es lo que escribirías en el programa ISQL o en un script:
CREATE DOMAIN D_IDENTIFICADOR AS BIGINT; CREATE DOMAIN D_CHAR3 AS CHAR(3); CREATE DOMAIN D_FECHA2010 AS DATE CHECK (VALUE >= '01-01-2010'); CREATE TABLE MOVIMCAB ( MVC_IDENTI D_IDENTIFICADOR NOT NULL, MVC_TIPMOV D_CHAR3, MVC_IDECLI D_IDENTIFICADOR, MVC_FECHAX D_FECHA2010); ALTER TABLE MOVIMCAB ADD CONSTRAINT PK_MOVIMCAB PRIMARY KEY (MVC_IDENTI); ALTER TABLE MOVIMCAB ADD CONSTRAINT FK_MOVIMCAB FOREIGN KEY (MVC_IDECLI) REFERENCES CLIENTES(CLI_IDENTI);
Conclusión:
Debemos usar mucho a las Foreign Keys porque con ellas se consigue que el contenido de nuestra Base de Datos sea altamente confiable. Si las usamos evitaremos que nos falten datos o que se realicen acciones inconsistentes, como borrar un producto del cual tenemos ventas registradas, o un alumno que ya se inscribió, o una cuenta contable que ya fue usada en un asiento, etc.
La regla de integridad NO ACTION es la más restrictiva y la que en general es mejor aplicar para no tener filas huérfanas, algo que en muchos casos puede ser muy malo, por eso es la regla de integridad por defecto.
Artículos relacionados:
Como distinguir el tipo de UPDATE | Firebird SQL
Jun 01, 2013 @ 12:26:18
El índice del blog Firebird21 | Firebird SQL
Jun 17, 2013 @ 04:45:44
Borrando filas de detalle en maestro/detalle | Firebird SQL
Jun 27, 2013 @ 06:33:12
Miquel
Nov 24, 2014 @ 16:03:41
Cómo puedo borrar una clave foránea en Firebird?
wrov
Nov 24, 2014 @ 19:59:44
Para que puedas borrar una clave foránea o Foreign Key el Firebird necesita saber:
1. El nombre de la tabla a la cual pertenece esa Foreign Key
2. El nombre de la Foreign Key que deseas borrar
Ejemplo:
ALTER TABLE MiTabla DROP CONSTRAINT MiClaveForanea
Como estarás modificando (o sea, alterando) la tabla entonces debes usar el comando ALTER TABLE. Y para borrar una restricción se usa DROP CONSTRAINT.
Saludos.
Walter.
Roy Salas - Costa Rica
Oct 12, 2015 @ 22:24:13
¿ Cuando creo registro de una venta con su correspondiente detalle, al ser en 2 tablas, debo ejecutar el commit primero para los registros en la tabla padre y luego proceder con el registro del detalle o el Firebird hace la verificación «en el aire» de que existe el registro padre (o por lo menos va a existir cuando se haga commit) y luego procede con todo el proceso de registro fijo al procesar el commit del detalle ?
No sé si me expliqué bien, lo planteo de nuevo, ¿ debo primero de hacer el registro en la tabla padre de forma independiente, con su respectivo commit ? y luego hacer el registro del detalle para que la FK haga la verificación de que existe el registro padre de los «hijos» (detalles) que se van a ingresar … o el Firebird «analiza» que el detalle que se está ingresando también lleva su registro padre al momento de hacer el último commit ?
Saludos.
wrov
Oct 12, 2015 @ 22:59:21
Una transacción no puede saber lo que las demás transacciones están haciendo pero sí sabe perfectamente bien lo que ella misma está haciendo.
Dentro de una transacción puedes tener todos los INSERT, UPDATE, DELETE y SELECT que quieras.
Lo normal para lo que planteas es lo siguiente:
– Se inicia la transacción
– Se guarda la fila cabecera. El stored procedure que guardó la cabecera devuelve el Identificador de esa fila
– Se guardan los detalles. Como se conoce cual es el identificador de la cabecera, se lo guarda en una columna de la tabla de detalles
– Se finaliza la transacción
En Firebird lo recomendable es que las transacciones sean muy cortas, por lo tanto entre el inicio y el fin de la transacción lo recomendable es que no realices ninguna tarea (como asignar valores a variables) que no sea una de las descritas arriba.
Saludos.
Walter.
seba
Oct 28, 2015 @ 23:36:33
para que exista clave foranea es necesario que ambas sea nprimarias?
wrov
Oct 29, 2015 @ 09:32:06
No. No es necesario, puedes relacionar con una clave única de la tabla padre también.
Saludos.
Walter.
Claudio
Abr 01, 2016 @ 12:07:57
Hola. Existe alguna forma en la base de datos para cambiar los mensajes de error de las foreign key que visualizan los usuarios por mensaje de que lo puedan entender.
wrov
Abr 03, 2016 @ 16:19:02
Hola
Cada mensaje de error tiene un número en la variable pública SQLSTATE.
En tu aplicación puedes capturar ese número de error y mostrarle al usuario cualquier mensaje que consideres apropiado.
Desde la versión 2.5.1, puedes capturar en el WHEN de la excepción el valor de SQLSTATE y devolver una excepción personalizada. O directamente en tu aplicación obtienes el valor de SQLSTATE y muestras el mensaje apropiado al usuario.
Saludos.
Walter.
Mpvcordoba
Abr 24, 2016 @ 12:54:39
Hola. Tengo un problema que no acierto a resolver. En una base de datos Firebird 1.5, una tabla maestra tiene un índice, sobre un campo IDMOV (integer) que no está declarado UNIQUE, aunque sus datos lo son.
Dicho campo esta referenciado por una tabla hija mediante una FK. Gran fallo de firebird 1.5, que permitía crear FKs sobre indices no declarados únicos en la tabla padre. Debido a esto no puedo actualizar la BD a 2.x.
He probado a crear otro índice único sobre el mismo campo de la tabla padre, y lo hace, pero no me deja borrar el primitivo indice erróneo de la tabla padre, ni tampoco declararlo único.
Aunque cree otro índice correcto y único en la tabla padre y recree la FK en la tabla hija, esta sigue usando el viejo índice no único. No sé dónde ni cómo indicarle a la nueva FK que recreo que use el nuevo índice único de la tabla padre. Siempre usa el viejo no único, el cual no puedo borrar ni declarar único, ni aun eliminando previamente la FK.
Gracias de antemano.
wrov
Abr 25, 2016 @ 13:34:28
Hola
¿Utilizas algún programa administrador gráfico, cómo el EMS SQL Manager, el IbExpert, el Flame Robin, o algún otro?
En mi caso, uso el EMS SQL Manager y cuando seleccionas una tabla tiene una opción que te muestra todas sus dependencias. Si no puedes eliminar un índice supongo que se lo utiliza en la FK de más de una tabla. Deberías averiguar cuales son esas tablas y eliminar sus FK.
Otro método, que funciona en el 100% de los casos es el siguiente:
1. Generas un archivo de script conteniendo la estructura completa de tu Base de Datos
2. Modificas ese archivo de script, borrando lo que quieras borrar, agregando lo que quieras agregar, cambiando lo que quieras cambiar
3. Actualizas la estructura de tu Base de Datos original con la estructura que se encuentra en tu archivo de script
Los 3 pasos anteriores puedes hacerlos con EMS SQL Manager y otros programas de administración gráfica.
Desde luego, antes de cambiar algo asegúrate de tener un BACKUP ACTUALIZADO Y FUNCIONAL de tu Base de Datos. Porque si «metes la pata» y haces algo indebido ese backup será tu salvación.
Saludos.
Walter.
Gastón
Ago 25, 2016 @ 20:04:05
Estoy comenzando con SQLiteStudio y esta explicación me sirvió de mucho, todo aplicable a sqlite, muchas gracias.