basics mysql foreign key constraint with examples
Este tutorial explica los conceptos básicos de MySQL FOREIGN KEY Constraint, como su sintaxis, cómo agregarlo, declararlo, eliminarlo y cambiarlo con ejemplos:
En términos muy simples, FOREIGN KEY se usa para vincular dos o más tablas en MySQL.
Las tablas MySQL deben estar conectadas para consultar y actualizar varios tipos de datos en diferentes momentos. Por lo tanto, es imperativo tener un punto de enlace entre 2 tablas.
En este tutorial, analizaremos los diferentes usos de las claves externas y cómo se pueden declarar y cambiar, y qué restricciones tiene en la estructura general de la tabla.
Lo que vas a aprender:
CLAVE EXTRANJERA de MySQL
Sintaxis:
|_+_|Arriba está la sintaxis utilizada al especificar FOREIGN KEY contra una tabla durante la creación de la tabla o con Sentencia ALTER TABLE.
Entendamos los diferentes componentes de la sintaxis:
- constrantName: Este es el nombre simbólico que queremos definir para la restricción FK que se especifica. Si se omite, el motor MySQL asigna automáticamente un nombre a la restricción FK.
- ReferenceColumnName: Esta es la columna que se referiría a los valores en otra tabla según lo especificado por la columna en la tabla referida.
- Tabla referida / tabla principal: Esto se refiere al nombre de la tabla desde la que se referirían los valores.
- Columna referida: El nombre de la columna en la tabla referida.
- Opción de referencia: Estas son las acciones que entran en escena cuando se realiza una acción de actualización o eliminación en la tabla que contiene la restricción de clave externa. Tanto UPDATE como DELETE pueden tener las mismas opciones de referencia o diferentes.
Aprenderíamos sobre diferentes acciones de integridad referencial más adelante en este tutorial.
Veamos un ejemplo de una referencia de LLAVE EXTRANJERA utilizando el ejemplo de Empleado / Departamento. Crearemos una tabla Departamento con columnas: departmentId (int & PRIMARY KEY) y departmentName (varchar).
|_+_|Cree una tabla Empleado con columnas como se muestra a continuación:
Columna | Escribe |
---|---|
identificación | INT (clave principal) |
nombre | VARCHAR |
dept_id | INT (clave externa) referenciada desde la tabla de departamentos |
dirección | VARCHAR |
edad | EN T |
dob | FECHA |
Como puede ver, en la tabla de empleados anterior, hemos declarado la columna deptId de tipo Int y hemos definido FOREIGN KEY de la tabla de departamentos en la columna departmentId.
Lo que esto significa esencialmente es que la columna deptId en la tabla Empleado solo puede contener valores que están en la tabla Departamento.
Intentemos insertar datos en estas tablas y veamos cómo funciona la RESTRICCIÓN DE CLAVE EXTRANJERA.
- Primero cree un registro en la tabla Departamento y agregue un registro en la tabla Empleado que haga referencia al ID del registro que se agregó a la tabla Departamento.
Verá que ambas declaraciones se ejecutarán sin errores.
- Ahora haga referencia a un valor para departmentId que no existe.
Por ejemplo, En la siguiente declaración de consulta, estamos creando un Empleado con un departmentId -10 inexistente
- En este escenario, obtendremos un error como el siguiente:
Por lo tanto, en términos generales, cuando se definen las referencias FOREIGN KEY, es importante asegurarse de que la tabla a la que se hace referencia tenga datos antes de que se haga referencia.
Acciones de integridad referencial
Primero intentemos entender qué es exactamente la integridad referencial.
La integridad referencial ayuda a mantener los datos en un estado limpio y coherente donde hay tablas relacionadas entre sí con una relación FOREIGN KEY.
En pocas palabras, la integridad referencial se refiere a la acción que esperamos que tome el motor de la base de datos, cuando ocurre una ACTUALIZACIÓN o ELIMINACIÓN en la tabla referenciada que contiene la CLAVE EXTRANJERA.
Por ejemplo, en nuestro ejemplo de Empleado / Departamento, suponga que cambiamos el ID de Departamento para una determinada fila en DB. Entonces, todas las filas de referencia en la tabla Empleado se verían afectadas. Podemos definir diferentes tipos de escenarios de integridad referencial que podrían aplicarse en tales casos.
Nota: La integridad referencial se define durante la configuración / declaración de FOREIGN KEY como parte de los comandos / secciones ON DELETE y ON UPDATE.
Consulte una consulta de muestra aquí (para el ejemplo de empleado / departamento):
|_+_|Inserte algunos datos en estas tablas como se muestra a continuación:
|_+_|Hay 4 acciones de referencia que son compatibles con MySQL. Intentemos comprender cada uno de ellos.
# 1) CASCADA
Esta es una de las acciones de integridad referencial más utilizadas. La configuración DELETE y UPDATE en CASCADE aplicaría los cambios realizados a la tabla referenciada en la tabla de referencia, es decir, en el ejemplo de Empleado / Departamento. Supongamos que alguien elimina una fila en la tabla Departamento que tiene que decir nombre_departamento = CONTABILIDAD, entonces todas las filas en la tabla Empleado que tengan id_departamento como la tabla Contabilidad también se eliminarán.
Entendamos esto con un ejemplo:
que es el archivo swf como abrirlo|_+_|
identificación | nombre | dirección | edad | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
2 | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
4 | DAVID BECKHAM | LONDRES | 40 | 1980-07-13 | 3 |
5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | 2 |
6 | FRANK BALDING | NUEVA YORK | 35 | 1985-08-25 | 5 |
Eliminar el registro de la tabla Departamento donde departmentName = 'CONTABILIDAD'
|_+_|Ahora, dado que se trata de una acción referencial en CASCADA, esperaríamos que todas las filas que tienen ID de departamento = 2 (que es para el departamento 'CONTABILIDAD') también se eliminen. Hagamos una consulta SELECT en la tabla de empleados nuevamente.
|_+_|identificación | nombre | dirección | edad | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
2 | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
4 | DAVID BECKHAM | LONDRES | 40 | 1980-07-13 | 3 |
6 | FRANK BALDING | NUEVA YORK | 35 | 1985-08-25 | 5 |
Como puede ver arriba, debido a la integridad referencial CASCADE, las filas en la tabla Empleado que se referían a la columna eliminada como EXTRANJERO CLAVE tendrán esas filas eliminadas.
# 2) RESTRICCIÓN / NO ACCIÓN
El modo RESTRICT o NO ACTION no permitirá ninguna operación UPDATE o DELETE en la tabla que tiene columnas a las que se hace referencia como FOREIGN KEY en alguna tabla.
El modo NO ACTION se puede aplicar simplemente omitiendo las cláusulas ON UPDATE y ON DELETE de la declaración de la tabla.
Intentemos el mismo ejemplo y, en este caso, simplemente omita la acción de integridad referencial ON UPDATE y ON DELETE.
Ahora, cuando intentamos eliminar cualquier entrada en la tabla a la que se hace referencia, obtendríamos un error ya que hemos configurado la acción referencial en RESTRICT.
Verá un error similar al siguiente si intenta ejecutar el comando DELETE anterior.
|_+_|# 3) ESTABLECER NULO
Con SET NULL, cualquier ACTUALIZACIÓN o ELIMINACIÓN en la tabla a la que se hace referencia haría que se actualice un valor NULO con respecto al valor de la columna que está marcado como una CLAVE EXTRANJERA en la tabla de referencia.
Con esta acción de integridad referencial, la definición de la tabla Empleado sería la siguiente:
|_+_|Elimine una fila en la tabla de referencia como se muestra a continuación:
|_+_|Ahora, en este caso, el valor al que se hace referencia en la tabla Empleado se establecería en NULL. Realice una consulta SELECT en la tabla Empleado para ver los resultados.
|_+_|identificación | nombre | dirección | edad | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
2 | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
4 | DAVID BECKHAM | LONDRES | 40 | 1980-07-13 | 3 |
5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | NULO |
6 | FRANK BALDING | NUEVA YORK | 35 | 1985-08-25 | 5 |
# 4) CONFIGURAR POR DEFECTO
El modo SET DEFAULT cuando se especifica daría como resultado la sustitución del valor predeterminado para la columna (como se especifica durante la declaración de la columna), en el caso de que se haga referencia a cualquier DELETES en la tabla.
Nota - Según Documentación de MySQL , la opción SET DEFAULT es compatible con MySQL Parser pero no con motores de base de datos como InnoDB. Esto podría ser compatible en el futuro.
Sin embargo, para soportar tal comportamiento, puede considerar usar SET NULL y definir un disparador en la tabla que podría establecer un valor predeterminado.
Agregar restricción FOREIGN KEY mediante la instrucción ALTER TABLE
Muchas veces puede suceder que deseemos agregar una restricción FOREIGN KEY a una tabla existente que no la tiene.
Supongamos que en el ejemplo de Empleado y Departamento, creamos una tabla de empleados sin ninguna restricción de CLAVE EXTRANJERA y luego queremos introducir la restricción. Esto se puede lograr usando el comando ALTER TABLE.
Intentemos entender esto con un ejemplo.
Supongamos que tenemos una tabla de empleados con la siguiente definición para el comando CREAR.
|_+_|Aquí, tenemos una columna deptId pero ninguna restricción FOREIGN KEY. En este caso, incluso sin tener una tabla de Departamento, podemos especificar cualquier valor al insertar registros.
Ahora, suponga más adelante que tenemos una tabla de Departamento separada y queremos vincular el ID de departamento allí como CLAVE EXTRANJERA a la tabla de Empleado.
|_+_|¿Qué pasa si esta tabla tiene datos existentes? ¿Podemos ALTERAR la tabla y agregar la restricción FOREIGN KEY?
La respuesta es sí, podemos con la condición de que los valores existentes en la columna a los que se hará referencia desde otra tabla tengan esos valores en la propia tabla principal.
Cree una tabla de empleados sin la restricción FOREIGN KEY, agregue algunos datos e intente agregar una restricción FOREIGN KEY utilizando el comando ALTER.
|_+_| |_+_|Cree una tabla de Departamento y agregue CLAVE EXTRANJERA contra el campo 'deptId' en la tabla de Empleados como se muestra a continuación:
|_+_|En este punto, si intentamos agregar la restricción FOREIGN KEY,
|_+_|Entonces obtendremos un error, ya que la tabla Empleado contiene algunos datos, pero la restricción de integridad referencial no se puede cumplir ya que la tabla Departamento aún no tiene datos.
|_+_|Para tener la restricción FOREIGN KEY, primero necesitaremos agregar datos a la tabla Departamento. Insertemos los registros necesarios en la tabla Departamento.
|_+_|Agregue la restricción FOREIGN KEY nuevamente ejecutando la misma instrucción ALTER TABLE. Notará que esta vez, el comando es exitoso y la tabla de Empleado se actualiza exitosamente para tener deptId como EXTRANJERO CLAVE de la tabla de Departamento.
Eliminación de una restricción de FOREIGN KEY
De manera similar a agregar una restricción FOREIGN KEY, también es posible eliminar / eliminar una restricción FOREIGN KEY existente de una tabla.
cómo hacer una prueba de penetración
Esto se puede lograr usando el comando ALTER TABLE.
Sintaxis:
|_+_|Aquí, 'childTable' es el nombre de la tabla que tiene definida la restricción FOREIGN KEY, mientras que el 'nombre de la restricción de clave foránea' es el nombre / símbolo que se utilizó para definir la FOREIGN KEY.
Veamos un ejemplo usando la tabla Empleado / Departamento. Para eliminar una restricción llamada 'depIdFk' de la tabla de empleados, use el siguiente comando:
|_+_|Preguntas frecuentes
P # 1) ¿Cómo puedo cambiar las claves externas en MySQL?
Responder: FOREGIN KEY se puede agregar / eliminar usando el comando ALTER TABLE.
Para cambiar o agregar una nueva CLAVE EXTRANJERA, puede usar el comando ALTER y definir la CLAVE EXTRANJERA y la columna de la tabla de referencia a la que se hará referencia desde la tabla secundaria.
P # 2) ¿Cómo configurar múltiples claves externas en MySQL?
Responder: Una tabla en MySQL puede tener varias LLAVES EXTRANJERAS, que podrían depender de la misma tabla principal o de diferentes tablas principales.
Usemos la tabla Empleado / Departamento y agreguemos FOREIGN KEY para el nombre del Departamento, así como el ID de Departamento en la tabla Empleado.
Consulte las declaraciones CREATE de ambas tablas como se muestra a continuación
|_+_|P # 3) ¿Cómo deshabilitar las restricciones de clave externa en MySQL?
Responder: Las restricciones FOREIGN KEY suelen ser necesarias cuando alguien intenta truncar una tabla existente a la que se hace referencia. Para hacer eso, puede usar el siguiente comando:
|_+_|Esto establecería una variable de sesión y deshabilitaría temporalmente FOREIGN_KEY_CHECKS. Después de esta configuración, puede continuar y realizar eliminaciones / truncar, lo que de otro modo no habría sido posible.
VPN para streaming
Pero asegúrese de que este es un privilegio de administrador y debe usarse con prudencia.
P # 4) ¿Cómo encuentro las referencias de claves foráneas para una tabla en MySQL?
Responder: Para enumerar todas las restricciones FOREIGN KEY que están presentes, puede utilizar la tabla 'INNODB_FOREIGN_COLS' en 'INFORMATION_SCHEMA'.
Simplemente ejecute el siguiente comando para obtener todas las declaraciones FOREIGN KEY existentes para una instancia de servidor MySQL determinada.
IDENTIFICACIÓN | FOR_COL_NAME | REF_COL_NAME | POS |
---|---|---|---|
my_sql_foreign_key / depIdFk | deptId | departmentId | 1 |
P # 5) ¿Debería la columna referenciada como LLAVE EXTRANJERA ser una clave principal en la tabla referenciada?
Responder: Por definición de LLAVE EXTRANJERA, se requeriría que la columna a la que se hace referencia como LLAVE EXTRANJERA sea la LLAVE PRINCIPAL de la tabla donde se hace referencia.
Sin embargo, con las versiones más recientes de MySQL y con el motor de base de datos InnoDB, también puede hacer referencia a una columna que tiene FOREIGN KEY que tiene una restricción ÚNICA y puede que no sea necesariamente PRIMARY KEY.
P # 6) ¿FOREIGN KEY crea ÍNDICE en MySQL?
Responder: Para la restricción de clave principal y única, MySQL crea automáticamente un ÍNDICE para dichas columnas.
Dado que ya sabemos que las referencias de FOREIGN KEY solo se pueden aplicar a columnas que son claves primarias o columnas que tienen valores únicos, por lo tanto, todas las columnas a las que se hace referencia como FOREIGN KEY tienen un índice creado contra ellas.
Para ver el índice en una tabla, use el siguiente comando:
|_+_|Entonces, para nuestro ejemplo de Empleado / Departamento, habíamos agregado deptId en Empleado como una CLAVE EXTRANJERA de la tabla Departamento.
Veamos los índices creados en las tablas Empleado y Departamento.
|_+_|Mesa | Non_unique | Nombre clave | Seq_in_index | Column_name | Colación | Cardinalidad | Sub_parte | Lleno | Nulo | Index_type |
---|---|---|---|---|---|---|---|---|---|---|
empleado | 0 | PRIMARIO | 1 | identificación | A | 0 | NULO | NULO | BTREE | |
empleado | 1 | depIdFk | 1 | deptId | A | 0 | NULO | NULO | SI | BTREE |
Puede ver 2 índices: uno es la clave principal para la tabla Empleado y otro es para el depósito de LLAVE EXTRANJERA al que se hace referencia en la tabla Departamento.
|_+_|Mesa | Non_unique | Nombre clave | Seq_in_index | Column_name | Colación | Cardinalidad | Sub_parte | Lleno | Nulo | Index_type |
---|---|---|---|---|---|---|---|---|---|---|
Departamento | 0 | PRIMARIO | 1 | departmentId | A | 0 | NULO | NULO | BTREE |
Aquí puede ver que para la tabla Departamento, solo tenemos 1 índice para la Clave principal (que se denomina LLAVE EXTRANJERA en la tabla Empleado).
P # 7) ¿Puede FOREIGN KEY ser NULL en MySQL?
Responder: Sí, está perfectamente bien tener NULL para la columna que tiene una dependencia de FOREIGN KEY en otra tabla. Esto también alude al hecho de que NULL no es un valor real, por lo tanto, no se compara con los valores de la tabla principal.
Conclusión
En este tutorial, aprendimos sobre diferentes conceptos relacionados con el uso de FOREIGN KEYS en bases de datos MySQL.
FOREIGN KEY facilita las actualizaciones y eliminaciones con las restricciones apropiadas, pero a veces tener muchas de estas relaciones puede hacer que todo el proceso de inserción y / o eliminación sea bastante engorroso.
Aprendimos cómo crear LLAVES EXTRANJERAS y cómo podemos actualizar y eliminar una LLAVE EXTERNA existente de la tabla secundaria. También aprendimos sobre diferentes acciones de integridad referencial y cómo podemos lograr diferentes comportamientos utilizando las diferentes opciones disponibles como CASCADE, NO ACTION, SET NULL, etc.
Lectura recomendada
- Tutorial de creación de tablas de MySQL con ejemplos
- Insertar MySQL en la tabla: sintaxis y ejemplos de instrucciones de inserción
- Tutorial de creación de vistas de MySQL con ejemplos de código
- Funciones MySQL CONCAT y GROUP_CONCAT con ejemplos
- Tutorial de transacciones MySQL con ejemplos de programación
- MySQL UNION: tutorial completo con ejemplos de unión
- Cómo descargar MySQL para Windows y Mac
- Diferencia entre SQL Vs MySQL Vs SQL Server (con ejemplos)