¿Cómo utilizar las restricciones de llave foránea o FOREIGN KEY de SQL Server?

En este tutorial, aprenderá a usar la restricción de llave foránea, externa o FOREIGN KEY de SQL Server para imponer un enlace entre los datos en dos tablas.

La llave foránea o FOREIGN KEY

La llave foránea o FOREIGN KEY, es una columna o varias columnas, que sirven para señalar cual es la llave primaria de otra tabla.

La columna o columnas señaladas como FOREIGN KEY, solo podrán tener valores que ya existan en la llave primaria PRIMARY KEY de la otra tabla.

La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. También controla que no pueda eliminarse un registro de una tabla ni modificar la llave primaria si una llave foránea o externa hace referencia al registro.

Introducción a la restricción de llave foránea, externa o FOREIGN KEY de SQL Server

Considere las siguientes tablas grupo_vendedores y de vendedores:

CREATE TABLE procurement.grupo_vendedores (
    grupo_id INT IDENTITY PRIMARY KEY,
    grupo_name VARCHAR (100) NOT NULL
);


CREATE TABLE procurement.vendedores(
        vendedor_id INT IDENTITY PRIMARY KEY,
        vendedor_name VARCHAR(100) NOT NULL,
        grupo_id INT NOT NULL,
);

Cada vendedor pertenece a un grupo de vendedores y cada grupo de vendedores puede tener cero o más vendedores. La relación entre las tablas grupo_vendedores y vendedores es de uno a muchos.

Para cada fila en la tabla de vendedores, siempre puede encontrar una fila correspondiente en la tabla grupo_vendedores.

Sin embargo, con la configuración de tablas, puede insertar una fila en la tabla de vendedores sin una fila correspondiente en la tabla grupo_vendedores. También puede eliminar una fila en la tabla grupo_vendedores sin actualizar o eliminar las filas correspondientes en la tabla de vendedores que da como resultado da filas huérfanas en la tabla de vendedores.

Para imponer el enlace entre los datos en las tablas grupo_vendedores y vendedores, debe establecer una llave foránea, externa o FOREIGN KEY en la tabla de vendedores.

Una llave foránea, externa o FOREIGN KEY es una columna o un grupo de columnas en una tabla que identifica de forma exclusiva una fila de otra tabla (o la misma tabla en caso de autorreferencia).
 

Para crear una llave foránea, usa la restricción FOREIGN KEY.

Las siguientes declaraciones eliminan la tabla de vendedores y la recrean con una restricción FOREIGN KEY:

DROP TABLE vendedores;
CREATE TABLE procurement.vendedores (
        vendedor_id INT IDENTITY PRIMARY KEY,
        vendedor_name VARCHAR(100) NOT NULL,
        grupo_id INT NOT NULL,
        CONSTRAINT fk_grupo FOREIGN KEY (grupo_id)
        REFERENCES procurement.grupo_vendedores (grupo_id)
);

La tabla grupo_vendedores es la tabla primaria a la cual se hace referencia en la restricción de llave foránea o FOREIGN KEY. La tabla de vendedores se denomina tabla secundaria, que es la tabla a la que se aplica la restricción de llave foránea o FOREIGN KEY.

En la declaración anterior, la siguiente cláusula crea una restricción FOREIGN KEY denominada fk_grupo que vincula el grupo_id en la tabla de vendedores al grupo_id en la tabla de grupo_vendedores:

CONSTRAINT fk_grupo FOREIGN KEY (grupo_id)
        REFERENCES procurement.grupo_vendedores (grupo_id)

Sintaxis de restricción FOREIGN KEY de SQL Server

La sintaxis general para crear una restricción FOREIGN KEY es la siguiente:

CONSTRAINT fk_constraint_name
FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

Examinemos esta sintaxis a detalle.

• Primero, especifique el nombre de restricción FOREIGN KEY después de la palabra llave CONSTRAINT. El nombre de la restricción es opcional, por lo tanto, es posible definir una restricción FOREIGN KEY de la siguiente manera:

  FOREIGN KEY (column_1, column2,...)
  REFERENCES parent_table_name(column1,column2,..)

  En este caso, SQL Server generará automáticamente un nombre para la restricción FOREIGN KEY.
• En segundo lugar, especifique una lista de columnas de llave foránea separadas por comas encerradas entre paréntesis después de la palabra clave FOREIGN KEY.
• En tercer lugar, especifique el nombre de la tabla primaria a la que hace referencia la llave foránea y una lista de columnas separadas por comas que tiene un enlace con la columna en la tabla secundaria.

Ejemplo de restricción FOREIGN KEY de SQL Server

• Primero, inserte algunas filas en la tabla grupo_vendedores:

  INSERT INTO procurement.grupo_vendedores(grupo_name)
  VALUES('Estrada Web Group Apps'),
        ('Estrada Web Group SQL Server),
        ('Estrada Web Group ASPNET');

   

• En segundo lugar, inserte un nuevo vendedor con un grupo de vendedores en la tabla de vendedores:

  INSERT INTO procurement.vendedores (vendedor_name, grupo_id)
  VALUES('Estrada Web Grouo Corp',1);

  
  La declaración funcionó como se esperaba.
• Tercero, intente insertar un nuevo vendedor cuyo grupo de vendedores no exista en la tabla grupo_vendedores

INSERT INTO procurement.vendedores(vendedor_name, grupo_id)
VALUES('EWG Corp',4);

SQL Server regresa el siguiente error:             

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_grupo". The conflict occurred in database "BikeStores", table "procurement.grupo_vendedores ", column grupo_id'.

En este ejemplo, debido a la restricción FOREIGN KEY, SQL Server rechazó la inserción y emitió un error.

Más información de referencia

La restricción de llave foránea FOREIGN KEY  garantiza la integridad referencial. Significa que solo puede insertar una fila en la tabla secundaria si hay una fila correspondiente en la tabla primaria.

Además, la restricción de llave foránea FOREIGN KEY le permite definir las acciones referenciales cuando la fila de la tabla primaria se actualiza o elimina de la siguiente manera:

FOREIGN KEY (foreign_key_columns)
    REFERENCES parent_table(parent_key_columns)
    ON UPDATE action
    ON DELETE action;

ON UPDATE y ON DELETE especifican qué acción se ejecutará cuando una fila de la tabla primaria se actualice o se elimine. Las siguientes son acciones permitidas: NO ACTION, CASCADE, SET NULL y SET DEFAULT

Acciones cuando se eliminan filas en la tabla primaria (ON DELETE)

Si elimina una o más filas en la tabla principal, puede establecer una de las siguientes acciones:

•     ON DELETE NO ACTION: SQL Server genera un error y revierte la acción de eliminación en la fila de la tabla primaria.
•     ON DELETE CASCADE: SQL Server elimina las filas de la tabla secundaria que corresponde a la fila eliminada de la tabla primaria.
•     ON DELETE SET NULL: SQL Server establece las filas de la tabla secundaria en NULL si se eliminan las filas correspondientes de la tabla primaria. Para ejecutar esta acción, las columnas de clave foránea deben aceptar valores nulos.
•     ON DELETE SET DEFAULT:  SQL Server establece las filas en la tabla secundaria a sus valores predeterminados si se eliminan las filas correspondientes en la tabla primaria. Para ejecutar esta acción, las columnas de clave foránea deben tener definiciones predeterminadas. Tenga en cuenta que una columna que acepta valores nulos tiene un valor predeterminado de NULL si no se especifica ningún valor predeterminado.

De forma predeterminada, SQL Server aplica ON DELETE NO ACTION si no especifica explícitamente ninguna acción.

Acciones cuando se modifican filas en la tabla primaria (ON UPDATE)

Si actualiza una o más filas en la tabla principal, puede establecer una de las siguientes acciones:

•     ON UPDATE NO ACTION: SQL Server genera un error y revierte la acción de actualización en la fila de la tabla principal.
•     ON UPDATE CASCADE: SQL Server actualiza las filas correspondientes en la tabla secundaria cuando se actualizan las filas en la tabla primaria.
•     ON UPDATE SET NULL: SQL Server establece las filas de la tabla secundaria en NULL cuando se actualiza la fila correspondiente en la tabla primaria. Tenga en cuenta que las columnas de clave foránea deben aceptar valores nulos para que esta acción se ejecute.
•     ON UPDATE SET DEFAULT: SQL Server establece los valores predeterminados para las filas de la tabla secundaria que tienen las filas correspondientes en la tabla primaria actualizada.

En este tutorial, ha aprendido cómo usar la restricción de clave foránea FOREIGN KEY de SQL Server para imponer la integridad referencial entre tablas.