Restricciones de clave primaria y foránea

Se aplica a: SQL Server 2016 (13.x) y posteriores Azure SQL Database Azure SQL Managed Instance

Las claves primarias y las claves foráneas son dos tipos de restricciones que pueden utilizarse para reforzar la integridad de los datos en las tablas de SQL Server. Son objetos importantes de la base de datos.

Este tema contiene las siguientes secciones.

Claves primarias

Claves foráneas

Tareas relacionadas

Claves primarias

Una tabla suele tener una columna o combinación de columnas que contienen valores que identifican de forma exclusiva cada fila de la tabla. Esta columna, o columnas, se denomina clave primaria (PK) de la tabla y refuerza la integridad de la entidad de la tabla. Dado que las restricciones de clave primaria garantizan la exclusividad de los datos, suelen definirse en una columna de identidad.

Cuando se especifica una restricción de clave primaria para una tabla, el Motor de Base de Datos refuerza la exclusividad de los datos creando automáticamente un índice único para las columnas de clave primaria. Este índice también permite un acceso rápido a los datos cuando la clave primaria se utiliza en las consultas. Si se define una restricción de clave primaria en más de una columna, los valores pueden duplicarse dentro de una columna, pero cada combinación de valores de todas las columnas en la definición de la restricción de clave primaria debe ser única.

Como se muestra en la siguiente ilustración, las columnas ProductID y VendorID de la tabla Purchasing.ProductVendor forman una restricción de clave primaria compuesta para esta tabla. Esto garantiza que cada fila de la tabla ProductVendor tenga una combinación única de ProductID y VendorID. Esto evita la inserción de filas duplicadas.

• Una tabla sólo puede contener una restricción de clave primaria.

• Una clave primaria no puede superar las 16 columnas y una longitud total de clave de 900 bytes.

• El índice generado por una restricción de clave primaria no puede hacer que el número de índices de la tabla supere los 999 índices no agrupados y 1 índice agrupado.

• Si no se especifica clúster o no clúster para una restricción de clave primaria, se utilizará clúster si no hay un índice clúster en la tabla.

• Todas las columnas definidas dentro de una restricción de clave primaria deben definirse como no nulas. Si no se especifica la anulabilidad, todas las columnas que participan en una restricción de clave primaria tienen su anulabilidad establecida como no nula.

• Si se define una clave primaria en una columna de tipo definido por el usuario de CLR, la implementación del tipo debe admitir el ordenamiento binario.

Restricciones de clave foránea

Una clave foránea (FK) es una columna o combinación de columnas que se utiliza para establecer y hacer cumplir un vínculo entre los datos de dos tablas para controlar los datos que pueden almacenarse en la tabla de clave foránea. En una referencia de clave externa, se crea un vínculo entre dos tablas cuando la columna o columnas que contienen el valor de clave primaria de una tabla son referenciadas por la columna o columnas de otra tabla. Esta columna se convierte en una clave foránea en la segunda tabla.

Por ejemplo, la tabla Ventas.CabeceraDePedidos tiene un enlace de clave foránea con la tabla Ventas.PersonaDeVentas porque existe una relación lógica entre los pedidos de ventas y los vendedores. La columna SalesPersonID de la tabla SalesOrderHeader coincide con la columna de clave primaria de la tabla SalesPerson. La columna SalesPersonID de la tabla SalesOrderHeader es la clave externa de la tabla SalesPerson. Al crear esta relación de clave foránea, no se puede insertar un valor para SalesPersonID en la tabla SalesOrderHeader si no existe ya en la tabla SalesPerson.

Una tabla puede hacer referencia a un máximo de 253 otras tablas y columnas como claves foráneas (referencias salientes). SQL Server 2016 (13.x) aumenta el límite del número de otras tablas y columnas que pueden hacer referencia a columnas de una misma tabla (referencias entrantes), de 253 a 10.000. (Requiere al menos el nivel de compatibilidad 130.) El aumento tiene las siguientes restricciones:

• Las referencias de clave externa superiores a 253 solo se admiten para las operaciones DML DELETE. Las operaciones UPDATE y MERGE no se admiten.

• Una tabla con una referencia de clave foránea a sí misma sigue estando limitada a 253 referencias de clave foránea.

• Las referencias de clave foránea superiores a 253 no están disponibles actualmente para índices de almacén de columnas, tablas optimizadas para memoria, Stretch Database o tablas de clave foránea con particiones.

Índices en restricciones de clave foránea

A diferencia de las restricciones de clave primaria, la creación de una restricción de clave foránea no crea automáticamente un índice correspondiente. Sin embargo, crear manualmente un índice sobre una clave foránea suele ser útil por las siguientes razones:

• Las columnas de clave foránea se utilizan con frecuencia en los criterios de unión cuando los datos de las tablas relacionadas se combinan en las consultas haciendo coincidir la columna o columnas de la restricción de clave foránea de una tabla con la columna o columnas de clave primaria o única de la otra tabla. Un índice permite al motor de la base de datos encontrar rápidamente los datos relacionados en la tabla de clave externa. Sin embargo, no es necesario crear este índice. Los datos de dos tablas relacionadas pueden combinarse incluso si no se definen restricciones de clave primaria o clave foránea entre las tablas, pero una relación de clave foránea entre dos tablas indica que las dos tablas se han optimizado para combinarse en una consulta que utiliza las claves como criterio.

• Los cambios en las restricciones de clave primaria se comprueban con las restricciones de clave foránea en las tablas relacionadas.

Integridad referencial

Aunque el objetivo principal de una restricción de clave foránea es controlar los datos que se pueden almacenar en la tabla de clave foránea, también controla los cambios en los datos de la tabla de clave primaria. Por ejemplo, si se elimina la fila de un vendedor de la tabla Ventas.PersonaVentas, y el ID del vendedor se utiliza para los pedidos de ventas en la tabla Ventas.CabeceraDePedidos, la integridad relacional entre las dos tablas se rompe; los pedidos de ventas del vendedor eliminado quedan huérfanos en la tabla CabeceraDePedidos sin un vínculo con los datos de la tabla PersonaVentas.

Una restricción de clave foránea evita esta situación. La restricción refuerza la integridad referencial garantizando que no se pueden realizar cambios en los datos de la tabla de clave primaria si esos cambios invalidan el vínculo con los datos de la tabla de clave foránea. Si se intenta eliminar la fila de una tabla de clave primaria o modificar un valor de clave primaria, la acción fallará cuando el valor de clave primaria eliminado o modificado corresponda a un valor de la restricción de clave externa de otra tabla. Para cambiar o eliminar con éxito una fila en una restricción de clave foránea, primero debe eliminar los datos de la clave foránea en la tabla de clave foránea o cambiar los datos de la clave foránea en la tabla de clave foránea, que vincula la clave foránea a datos de clave primaria diferentes.

Integridad referencial en cascada

Al utilizar restricciones de integridad referencial en cascada, puede definir las acciones que el Motor de Base de Datos realiza cuando un usuario intenta eliminar o actualizar una clave a la que apuntan las claves foráneas existentes. Se pueden definir las siguientes acciones en cascada.

NO ACCIÓN
El Motor de Base de Datos genera un error y la acción de eliminación o actualización de la fila en la tabla padre se revierte.

CASCADE
Las filas correspondientes se actualizan o eliminan en la tabla de referencia cuando esa fila se actualiza o elimina en la tabla padre. No se puede especificar CASCADE si una columna de marca de tiempo forma parte de la clave foránea o de la clave referenciada. No se puede especificar ON DELETE CASCADE para una tabla que tenga un trigger INSTEAD OF DELETE. ON UPDATE CASCADE no puede especificarse para tablas que tengan triggers INSTEAD OF UPDATE.

SET NULL
Todos los valores que componen la clave foránea se establecen como NULL cuando la fila correspondiente de la tabla padre se actualiza o se elimina. Para que esta restricción se ejecute, las columnas de la clave foránea deben ser anulables. No se puede especificar para las tablas que tienen desencadenantes INSTEAD OF UPDATE.

SET DEFAULT
Todos los valores que componen la clave foránea se establecen en sus valores por defecto si la fila correspondiente de la tabla padre se actualiza o se elimina. Para que esta restricción se ejecute, todas las columnas de la clave foránea deben tener definiciones por defecto. Si una columna es anulable y no hay un valor predeterminado explícito establecido, NULL se convierte en el valor predeterminado implícito de la columna. No se puede especificar para tablas que tengan triggers INSTEAD OF UPDATE.

CASCADE, SET NULL, SET DEFAULT y NO ACTION se pueden combinar en tablas que tengan relaciones referenciales entre sí. Si el motor de la base de datos encuentra NO ACTION, detiene y revierte las acciones CASCADE, SET NULL y SET DEFAULT relacionadas. Cuando una sentencia DELETE provoca una combinación de acciones CASCADE, SET NULL, SET DEFAULT y NO ACTION, todas las acciones CASCADE, SET NULL y SET DEFAULT se aplican antes de que el Motor de Base de Datos compruebe cualquier NO ACTION.

Triggers y acciones referenciales en cascada

Las acciones referenciales en cascada disparan los triggers AFTER UPDATE o AFTER DELETE de la siguiente manera:

• Todas las acciones referenciales en cascada directamente causadas por el DELETE o UPDATE original se realizan primero.

• Si hay algún desencadenante AFTER definido en las tablas afectadas, estos desencadenantes se disparan después de que se realicen todas las acciones en cascada. Estos desencadenantes se disparan en orden inverso a la acción en cascada. Si hay varios desencadenantes en una misma tabla, se disparan en orden aleatorio, a menos que haya un primer o último desencadenante dedicado a la tabla. Este orden se especifica utilizando sp_settriggerorder.

• Si varias cadenas en cascada se originan en la tabla que fue el objetivo directo de una acción UPDATE o DELETE, el orden en que estas cadenas disparan sus respectivos triggers no está especificado. Sin embargo, una cadena siempre dispara todos sus desencadenantes antes de que otra cadena comience a disparar.

• Un desencadenante AFTER en la tabla que es el objetivo directo de una acción UPDATE o DELETE se dispara independientemente de si hay filas afectadas. En este caso no hay otras tablas afectadas por la cascada.

• Si alguno de los desencadenantes anteriores realiza operaciones UPDATE o DELETE en otras tablas, estas acciones pueden iniciar cadenas secundarias en cascada. Estas cadenas secundarias se procesan para cada operación UPDATE o DELETE a la vez después de que se disparen todos los triggers de todas las cadenas primarias. Este proceso puede repetirse recursivamente para operaciones UPDATE o DELETE posteriores.

• La realización de operaciones CREATE, ALTER, DELETE u otras operaciones de lenguaje de definición de datos (DDL) dentro de los desencadenantes puede hacer que se disparen desencadenantes DDL. Esto puede realizar posteriormente operaciones DELETE o UPDATE que inician cadenas en cascada y desencadenantes adicionales.

• Si se genera un error dentro de cualquier cadena de acción referencial en cascada particular, se genera un error, no se disparan desencadenantes AFTER en esa cadena y la operación DELETE o UPDATE que creó la cadena se revierte.

• Una tabla que tiene un disparador INSTEAD OF no puede tener también una cláusula REFERENCES que especifique una acción en cascada. Sin embargo, un desencadenante AFTER en una tabla a la que se dirige una acción en cascada puede ejecutar una sentencia INSERT, UPDATE o DELETE en otra tabla o vista que dispara un desencadenante INSTEAD OF definido en ese objeto.

Tareas relacionadas

La siguiente tabla enumera las tareas comunes asociadas con las restricciones de clave primaria y clave foránea.

Describe cómo crear una clave primaria.

Tarea	Tema
Crear claves primarias
Describe cómo eliminar una clave primaria.	Eliminar claves primarias
Describe cómo modificar una clave primaria.	Modificar claves primarias
Describe cómo crear relaciones de clave foránea	Crear relaciones de clave foránea
Describe cómo modificar relaciones de clave foránea.	Modificar relaciones de clave foránea
Describe cómo eliminar relaciones de clave foránea.	Eliminar relaciones de clave foránea	Describe cómo ver las propiedades de clave foránea.	Ver propiedades de clave foránea
Describe cómo desactivar las restricciones de clave foránea para la replicación.	Desactivar restricciones de clave foránea para la replicación
Describe cómo desactivar las restricciones de clave foránea durante una sentencia INSERT o UPDATE.	Desactivar restricciones de clave foránea con sentencias INSERT y UPDATE