Microsoft SQL Server
Llaves extranjeras

Relación / restricción de clave externa

Las claves externas le permiten definir la relación entre dos tablas. Una tabla (principal) debe tener una clave principal que identifique de forma única las filas de la tabla. Otra tabla (secundaria) puede tener el valor de la clave primaria de la matriz en una de las columnas. La restricción FOREIGN KEY REFERENCES garantiza que los valores en la tabla secundaria deben existir como un valor de clave principal en la tabla principal.

En este ejemplo, tenemos la tabla de la Compañía principal con la clave principal de CompanyId y la tabla de Empleado secundario que tiene el ID de la compañía donde trabaja este empleado.

create table Company (
   CompanyId int primary key,
   Name nvarchar(200)
)
create table Employee (
    EmployeeId int,
    Name nvarchar(200),
    CompanyId int
        foreign key references Company(companyId)
)

las referencias de clave externa garantizan que los valores insertados en la columna Employee.CompanyId también deben existir en la columna Company.CompanyId. Además, nadie puede eliminar la compañía en la tabla de la compañía si hay al menos un empleado con una companyId coincidente en la tabla secundaria.

La relación FOREIGN KEY garantiza que las filas de dos tablas no puedan "desvincularse".

Mantener la relación entre las filas padre / hijo

Supongamos que tenemos una fila en la tabla Empresa con companyId 1. Podemos insertar una fila en la tabla de empleados que tiene companyId 1:

insert into Employee values (17, 'John', 1)

Sin embargo, no podemos insertar un empleado que no tenga un ID de empresa:

insert into Employee values (17, 'John', 111111)

Msg 547, nivel 16, estado 0, línea 12 La instrucción INSERT entró en conflicto con la restricción FOREIGN KEY "FK__Employee__Compan__1EE485AA". El conflicto se produjo en la base de datos "MyDb", tabla "dbo.Company", columna 'CompanyId'. La instrucción se ha terminado.

Además, no podemos eliminar la fila principal en la tabla de la compañía siempre que haya al menos una fila secundaria en la tabla de empleados que la referencia.

delete from company where CompanyId = 1

Msg 547, nivel 16, estado 0, línea 14 La instrucción DELETE entró en conflicto con la restricción de REFERENCIA "FK__Employee__Compan__1EE485AA". El conflicto se produjo en la base de datos "MyDb", tabla "dbo.Employee", columna 'CompanyId'. La instrucción se ha terminado.

La relación de clave externa garantiza que las filas de la empresa y los empleados no se "desvincularán".

Agregar una relación de clave externa en la tabla existente

La restricción FOREIGN KEY se puede agregar a las tablas existentes que todavía no están en relación. Imagine que tenemos tablas de Empresa y Empleado en las que la columna de Id. Compañía de la tabla de Empleado no tiene una relación de clave externa. La declaración ALTER TABLE le permite agregar una restricción de clave externa en una columna existente que hace referencia a otra tabla y clave principal en esa tabla:

alter table Employee
    add  foreign key (CompanyId) references Company(CompanyId)

Añadir clave externa en la tabla existente

Las columnas FOREIGN KEY con restricción se pueden agregar a las tablas existentes que aún no están en relación. Imagine que tenemos tablas de Empresa y Empleado donde la tabla de Empleado no tiene la columna CompanyId. La declaración ALTER TABLE le permite agregar una nueva columna con una restricción de clave externa que hace referencia a otra tabla y clave principal en esa tabla:

alter table Employee
    add CompanyId int foreign key references Company(CompanyId)

Obtener información sobre restricciones de clave externa

La vista del sistema sys.foreignkeys devuelve información sobre todas las relaciones de clave externa en la base de datos:

select name,
 OBJECT_NAME(referenced_object_id) as [parent table],
 OBJECT_NAME(parent_object_id) as [child table],
 delete_referential_action_desc,
 update_referential_action_desc
from sys.foreign_keys