Una clave externa es un conjunto de atributos en una tabla que se refiere a la clave principal de otra tabla. La clave externa vincula estas dos tablas. Otra forma de decirlo: en el contexto de las bases de datos relacionales , una clave externa es un conjunto de atributos sujetos a cierto tipo de restricciones de dependencia de inclusión, específicamente una restricción de que las tuplas que consisten en los atributos de clave externa en una relación , R, deben también existen en alguna otra relación (no necesariamente distinta), S, y además esos atributos también deben ser una clave candidata en S. [1] [2] [3]En palabras más simples, una clave externa es un conjunto de atributos que hace referencia a una clave candidata. Por ejemplo, una tabla llamada EQUIPO puede tener un atributo, MEMBER_NAME, que es una clave externa que hace referencia a una clave candidata, PERSON_NAME, en la tabla PERSON. Dado que MEMBER_NAME es una clave externa, cualquier valor existente como el nombre de un miembro en TEAM también debe existir como el nombre de una persona en la tabla PERSON; en otras palabras, cada miembro de un EQUIPO es también una PERSONA.
Resumen
La tabla que contiene la clave externa se denomina tabla secundaria y la tabla que contiene la clave candidata se denomina tabla principal o referenciada. [4] En el modelado e implementación relacional de bases de datos, una clave candidata es un conjunto de cero o más atributos, cuyos valores están garantizados como únicos para cada tupla (fila) en una relación. El valor o la combinación de valores de los atributos clave candidatos para cualquier tupla no se puede duplicar para ninguna otra tupla en esa relación.
Dado que el propósito de la clave externa es identificar una fila particular de la tabla referenciada, generalmente se requiere que la clave externa sea igual a la clave candidata en alguna fila de la tabla principal, o de lo contrario no tenga ningún valor (el valor NULL . [ 2] ). Esta regla se denomina restricción de integridad referencial entre las dos tablas. [5] Debido a que las violaciones de estas restricciones pueden ser la fuente de muchos problemas de bases de datos, la mayoría de los sistemas de administración de bases de datos proporcionan mecanismos para garantizar que cada clave externa no nula corresponda a una fila de la tabla referenciada. [6] [7] [8]
Por ejemplo, considere una base de datos con dos tablas: una tabla CLIENTE que incluye todos los datos del cliente y una tabla PEDIDO que incluye todos los pedidos de los clientes. Suponga que la empresa requiere que cada pedido se refiera a un solo cliente. Para reflejar esto en la base de datos, se agrega una columna de clave externa a la tabla ORDER (por ejemplo, CUSTOMERID), que hace referencia a la clave principal del CLIENTE (por ejemplo, ID). Debido a que la clave principal de una tabla debe ser única, y debido a que CUSTOMERID solo contiene valores de ese campo de clave principal, podemos asumir que, cuando tiene un valor, CUSTOMERID identificará al cliente en particular que realizó el pedido. Sin embargo, esto ya no se puede suponer si la tabla ORDER no se mantiene actualizada cuando se eliminan las filas de la tabla CLIENTE o se modifica la columna ID, y trabajar con estas tablas puede resultar más difícil. Muchas bases de datos del mundo real solucionan este problema "inactivando" en lugar de eliminar físicamente las claves externas de la tabla maestra, o mediante programas de actualización complejos que modifican todas las referencias a una clave externa cuando se necesita un cambio.
Las claves externas juegan un papel esencial en el diseño de bases de datos . Una parte importante del diseño de la base de datos es asegurarse de que las relaciones entre las entidades del mundo real se reflejen en la base de datos mediante referencias, utilizando claves externas para hacer referencia de una tabla a otra. [9] Otra parte importante del diseño de la base de datos es la normalización de la base de datos , en la que las tablas se dividen y las claves externas hacen posible su reconstrucción. [10]
Varias filas en la tabla de referencia (o secundaria) pueden hacer referencia a la misma fila en la tabla de referencia (o principal). En este caso, la relación entre las dos tablas se denomina relación de uno a muchos entre la tabla de referencia y la tabla de referencia.
Además, la tabla secundaria y la tabla principal pueden, de hecho, ser la misma tabla, es decir, la clave externa hace referencia a la misma tabla. Dicha clave externa se conoce en SQL: 2003 como una clave externa autorreferencial o recursiva. En los sistemas de administración de bases de datos, esto a menudo se logra vinculando una primera y una segunda referencia a la misma tabla.
Una tabla puede tener varias claves externas y cada clave externa puede tener una tabla principal diferente. El sistema de base de datos aplica cada clave externa de forma independiente . Por lo tanto, las relaciones en cascada entre tablas se pueden establecer utilizando claves externas.
Una clave externa se define como un atributo o conjunto de atributos en una relación cuyos valores coinciden con una clave primaria en otra relación. La sintaxis para agregar dicha restricción a una tabla existente se define en SQL: 2003 como se muestra a continuación. Omitir la lista de columnas en la REFERENCES
cláusula implica que la clave externa hará referencia a la clave principal de la tabla a la que se hace referencia. Asimismo, las claves externas se pueden definir como parte de la CREATE TABLE
instrucción SQL.
CREAR TABLA child_table ( col1 INTEGER PRIMARIA CLAVE , col2 CARÁCTER VARYING ( 20 ), col3 INTEGER , col4 INTEGER , EXTERIOR KEY ( col3 , col4 ) Referencias parent_table ( col1 , col2 ) EN DELETE CASCADE )
Si la clave externa es solo una columna, la columna se puede marcar como tal usando la siguiente sintaxis:
CREATE TABLE child_table ( col1 INTEGER PRIMARY KEY , col2 CHARACTER VARYING ( 20 ), col3 INTEGER , col4 INTEGER REFERENCES parent_table ( col1 ) ON DELETE CASCADE )
Las claves externas se pueden definir con una instrucción de procedimiento almacenado .
sp_foreignkey child_table , parent_table , col3 , col4
- child_table : el nombre de la tabla o vista que contiene la clave externa que se va a definir.
- parent_table : el nombre de la tabla o vista que tiene la clave principal a la que se aplica la clave externa. La clave primaria ya debe estar definida.
- col3 y col4 : el nombre de las columnas que componen la clave externa. La clave externa debe tener al menos una columna y como máximo ocho columnas.
Acciones referenciales
Debido a que el sistema de administración de la base de datos impone restricciones de referencia, debe garantizar la integridad de los datos si las filas de una tabla referenciada deben eliminarse (o actualizarse). Si todavía existen filas dependientes en las tablas de referencia, esas referencias deben tenerse en cuenta. SQL: 2003 especifica 5 acciones referenciales diferentes que se llevarán a cabo en tales ocurrencias:
CASCADA
Siempre que se eliminen (o actualicen) las filas de la tabla principal (referenciada), también se eliminarán (o actualizarán) las filas respectivas de la tabla secundaria (referenciada) con una columna de clave externa coincidente. Esto se denomina eliminación (o actualización) en cascada.
RESTRINGIR
Un valor no se puede actualizar ni eliminar cuando existe una fila en una tabla de referencia o secundaria que hace referencia al valor en la tabla a la que se hace referencia.
Del mismo modo, una fila no se puede eliminar siempre que haya una referencia a ella desde una tabla secundaria o de referencia.
Para comprender mejor RESTRICT (y CASCADE), puede ser útil notar la siguiente diferencia, que puede no estar clara de inmediato. La acción referencial CASCADE modifica el "comportamiento" de la propia tabla (secundaria) donde se utiliza la palabra CASCADE. Por ejemplo, ON DELETE CASCADE dice efectivamente "Cuando la fila a la que se hace referencia se elimine de la otra tabla (tabla maestra), entonces elimínela también de mí ". Sin embargo, la acción referencial RESTRICT modifica el "comportamiento" de la tabla maestra, no la tabla secundaria, ¡aunque la palabra RESTRICT aparece en la tabla secundaria y no en la tabla maestra! Entonces, ON DELETE RESTRICT dice efectivamente: "Cuando alguien intenta eliminar la fila de la otra tabla (tabla maestra), evite la eliminación de esa otra tabla (y, por supuesto, tampoco elimine de mí, pero ese no es el punto principal aquí)."
RESTRICT no es compatible con Microsoft SQL 2012 y versiones anteriores.
NO HAY ACCION
NINGUNA ACCIÓN y RESTRICCIÓN son muy parecidas. La principal diferencia entre NO ACTION y RESTRICT es que SIN ACCIÓN, la verificación de integridad referencial se realiza después de intentar alterar la tabla. RESTRICT realiza la comprobación antes de intentar ejecutar la instrucción UPDATE o DELETE . Ambas acciones referenciales actúan de la misma manera si falla la verificación de integridad referencial: la instrucción UPDATE o DELETE dará como resultado un error.
En otras palabras, cuando se ejecuta una instrucción UPDATE o DELETE en la tabla referenciada usando la acción referencial NO ACTION, el DBMS verifica al final de la ejecución de la instrucción que no se viola ninguna de las relaciones referenciales. Esto es diferente de RESTRICT, que asume desde el principio que la operación violará la restricción. Usando NO ACTION, los desencadenantes o la semántica de la declaración en sí pueden producir un estado final en el que no se violen relaciones de clave externa para cuando finalmente se verifique la restricción, lo que permite que la declaración se complete con éxito.
SET NULL, SET DEFAULT
En general, la acción realizada por el DBMS para SET NULL o SET DEFAULT es la misma para ON DELETE o ON UPDATE: el valor de los atributos de referencia afectados se cambia a NULL para SET NULL y al valor predeterminado especificado para SET DEFAULT .
Disparadores
Las acciones referenciales se implementan generalmente como activadores implícitos (es decir, activadores con nombres generados por el sistema, a menudo ocultos). Como tales, están sujetos a las mismas limitaciones que los activadores definidos por el usuario, y su orden de ejecución en relación con otros activadores puede necesitar ser considerado; en algunos casos, puede ser necesario reemplazar la acción referencial con su disparador definido por el usuario equivalente para asegurar el orden de ejecución adecuado, o para evitar las limitaciones de la tabla mutante.
Otra limitación importante aparece con el aislamiento de transacciones : es posible que sus cambios en una fila no puedan ser completamente en cascada porque la fila es referenciada por datos que su transacción no puede "ver" y, por lo tanto, no puede hacerlo en cascada. Un ejemplo: mientras su transacción intenta volver a numerar la cuenta de un cliente, una transacción simultánea intenta crear una nueva factura para ese mismo cliente; mientras que una regla CASCADE puede arreglar todas las filas de facturas que su transacción puede ver para mantenerlas consistentes con la fila del cliente renumerada, no llegará a otra transacción para arreglar los datos allí; Debido a que la base de datos no puede garantizar datos consistentes cuando las dos transacciones se confirman, una de ellas se verá obligada a retroceder (a menudo por orden de llegada).
CREAR TABLA cuenta ( acct_num INT , monto DECIMAL ( 10 , 2 ));CREAR DISPARADOR ins_sum ANTES DE INSERTAR EN la cuenta PARA CADA FILA SET @ sum = @ sum + NEW . ascender ;
Ejemplo
Como primer ejemplo para ilustrar las claves externas, suponga que una base de datos de cuentas tiene una tabla con facturas y cada factura está asociada con un proveedor en particular. Los detalles del proveedor (como el nombre y la dirección) se guardan en una tabla separada; a cada proveedor se le asigna un 'número de proveedor' para identificarlo. Cada registro de factura tiene un atributo que contiene el número de proveedor de esa factura. Entonces, el 'número de proveedor' es la clave principal en la tabla de proveedores. La clave externa en la tabla Factura apunta a esa clave primaria. El esquema relacional es el siguiente. Las claves primarias están marcadas en negrita y las claves externas están marcadas en cursiva.
Proveedor (número de proveedor , nombre, dirección) Factura ( InvoiceNumber , Text, SupplierNumber )
La declaración correspondiente del lenguaje de definición de datos es la siguiente.
CREATE TABLE Proveedor ( SupplierNumber INTEGER NOT NULL , Name VARCHAR ( 20 ) NOT NULL , Address VARCHAR ( 50 ) NOT NULL , CONSTRAINT proveedor_pk PRIMARY KEY ( SupplierNumber ), CONSTRAINT number_value CHECK ( SupplierNumber > 0 ) )CREAR TABLA factura ( NúmerodeFactura INTEGER NOT NULL , texto VARCHAR ( 4096 ), SupplierNumber INTEGER NOT NULL , CONSTRAINT invoice_pk PRIMARIA CLAVE ( NúmerodeFactura ), CONSTREÑIMIENTO inumber_value COMPROBAR ( NúmerodeFactura > 0 ), CONSTREÑIMIENTO supplier_fk EXTERIOR CLAVE ( SupplierNumber ) Referencias Proveedor ( SupplierNumber ) EN ACTUALIZAR CASCADA EN ELIMINAR RESTRICT )
Ver también
Referencias
- ^ Coronel, Carlos (2010). Sistemas de bases de datos: diseño, implementación y gestión . Independence KY: South-Western / Cengage Learning. pag. 65. ISBN 978-0-538-74884-1.
- ^ a b Elmasri, Ramez (2011). Fundamentos de los sistemas de bases de datos . Addison-Wesley. págs. 73 –74. ISBN 978-0-13-608620-8.
- ^ Fecha, CJ (1996). Una guía para el estándar SQL . Addison-Wesley. pag. 206. ISBN 978-0201964264.
- ^ Sheldon, Robert (2005). Comenzando con MySQL . John Wiley e hijos. págs. 119-122. ISBN 0-7645-7950-9.
- ^ "Conceptos básicos de la base de datos: claves externas" . Consultado el 13 de marzo de 2010 .
- ^ MySQL AB (2006). Guía del administrador de MySQL y referencia del lenguaje . Sams Publishing. pag. 40. ISBN 0-672-32870-4.
- ^ Powell, Gavin (2004). Oracle SQL: Jumpstart con ejemplos . Elsevier. pag. 11 . ASIN B008IU3AHY .
- ^ Mullins, Craig (2012). Guía del desarrollador de DB2 . IBM Press. ASIN B007Y6K9TK .
- ^ Sheldon, Robert (2005). Comenzando con MySQL . John Wiley e hijos. pag. 156. ISBN 0-7645-7950-9.
- ^ García-Molina, Héctor (2009). Sistemas de bases de datos: el libro completo . Prentice Hall. pp. 93 -95. ISBN 978-0-13-187325-4.
enlaces externos
- Claves externas SQL-99
- Claves externas de PostgreSQL
- Claves externas de MySQL
- Claves primarias de FirebirdSQL
- Soporte SQLite para claves externas
- Table_constraint de Microsoft SQL 2012 (Transact-SQL)