Oracle Foreign Key

Resumen: en este tutorial, aprenderá a utilizar la clave externa o Foreign Key de Oracle 12c para establecer la relación entre tablas.

• Introducción a la restricción de clave externa de Oracle
• Restricción de clave externa de Oracle en acciones
• Sintaxis de restricción de clave externa de Oracle
• Crear una restricción de clave externa o Foreign Key
• Agregar una restricción de Foreign Key a una tabla
• Eliminar una restricción de Foreign Key
• Habilitar una restricción externa

Introducción a la restricción de clave externa de Oracle

Una clave externa tiene que ver con la relación. Comencemos con un ejemplo para comprender claramente su concepto.

Supongamos que tenemos dos tablas supplier_groups y suppliers:

CREATE TABLE supplier_groups(
    group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    group_name VARCHAR2(255) NOT NULL,
    PRIMARY KEY (group_id)  
);

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id)
);

La tabla de suppliers almacena la información del proveedor. Cada proveedor debe pertenecer a un grupo de proveedores.

La relación entre los supplier_groups y la tabla de suppliers es de uno a muchos. En otras palabras, un grupo de proveedores tiene muchos proveedores, mientras que cada suppliers debe pertenecer a un supplier_groups

El group_id en la tabla de proveedores se utiliza para establecer la relación entre las filas de las tablas de suppliers y supplier_groups.

Antes de insertar una fila en la tabla de proveedores, debe buscar un group_id existente en la tabla de supplier_groups y usar este valor para la inserción.

Suponiendo que la tabla Supplier_groups contiene los siguientes datos:

INSERT INTO supplier_groups(group_name) 
VALUES('One-time Supplier');

INSERT INTO supplier_groups(group_name) 
VALUES('Third-party Supplier');

INSERT INTO supplier_groups(group_name)
VALUES('Inter-co Supplier');

SELECT
    *
FROM
    supplier_groups;

Para insertar un nuevo proveedor externo, debe usar group_id 2 de la siguiente manera:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('Toshiba',1);

Funciona perfectamente bien. Sin embargo, la siguiente declaración también funciona:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('WD',4);

La tabla Supplier_groups no tiene una fila con el ID de grupo 4 pero nada le impide insertarla en la tabla de proveedores, lo cual es un problema.

Por ejemplo, la siguiente consulta no obtiene todos los proveedores y sus grupos:

SELECT
    supplier_name,
    group_name
FROM
    suppliers
INNER JOIN supplier_groups
        USING(group_id);

Como puede ver, el proveedor de WD falta en el conjunto de resultados.

Una solución para solucionar este problema es utilizar la restricción de clave externa de Oracle para hacer cumplir la relación entre las filas de las tablas de supplier_groups y proveedores.

Primero, borre la tabla de suppliers:

DROP TABLE suppliers;

En segundo lugar, vuelva a crear la tabla de proveedores con una restricción de clave externa:

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id),
    FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
);

En esta declaración, se agregó recientemente la siguiente cláusula:

FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)

Esta cláusula define la columna group_id en la tabla de proveedores como una clave externa que hace referencia a la columna group_id de la tabla Supplier_groups.

De esta manera, Oracle aplica la restricción. En otras palabras, intentar insertar una fila en la tabla de suppliers que no corresponde a ninguna fila en la tabla de supplier_groups fallará, ya que intentar eliminar una fila de la tabla de supplier_groups donde existen filas dependientes en la tabla de suppliers.

La tabla de suppliers se denomina tabla secundaria, mientras que los supplier_groups se denominan tabla principal. Para ampliar la analogía padre-hijo, el valor de la clave principal se toma de la tabla principal (Supplier_groups) y se inserta en la tabla secundaria (Suppliers), es decir, el hijo lleva una CLAVE EXTRANJERA o FORANEA como el ADN o código genético del padre.

Por cierto, el concepto de integridad referencial se trata de mantener y hacer cumplir esta relación entre padres e hijos.

Restricción de clave externa de Oracle en acciones

La siguiente declaración funciona porque la tabla Supplier_groups tiene una fila con group_id 1:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('Toshiba',1);

Sin embargo, la siguiente declaración fallará:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('WD',4);

Porque el Supplier_groups no tiene una fila con el ID 4. El siguiente es el mensaje de error:
SQL Error: ORA-02291: integrity constraint (OT.SYS_C0010646) violated – parent key not found
De manera similar, el intento de eliminar una fila con group_id 1 en la tabla Supplier_groups fallará:

DELETE
FROM
    supplier_groups
WHERE
    group_id = 1;

Oracle emitió el siguiente mensaje de error:
SQL Error: ORA-02292: integrity constraint (OT.SYS_C0010654) violated – child record found
Porque la tabla de Supplier (tabla secundaria) tiene una fila que hace referencia a la fila que se está eliminando.

Sintaxis de restricción de clave externa de Oracle

Oracle le permite crear, agregar, eliminar, deshabilitar y habilitar una restricción de clave externa.

Crear una restricción de clave externa o Foreign Key

La siguiente declaración ilustra la sintaxis de la creación de una restricción de clave externa cuando crea una tabla:

CREATE TABLE child_table (
    ...
    CONSTRAINT fk_name
    FOREIGN KEY(col1, col2,...) REFERENCES parent_table(col1,col2) 
    ON DELETE [ CASCADE | SET NULL ]
);

Examinemos la declaración en detalle.

Primero, para asignar explícitamente un nombre a la restricción de clave externa, use la cláusula CONSTRAINT seguida del nombre. La cláusula CONSTRAINT es opcional. Si lo omite, Oracle asignará un nombre generado por el sistema a la restricción de clave externa.

En segundo lugar, especifique la cláusula FOREIGN KEY para definir una o más columnas como una clave externa y una tabla principal con columnas a las que hacen referencia las columnas de la clave externa.

En tercer lugar, utilice la cláusula ON DELETE para especificar la consecuencia cuando se eliminan las filas de la tabla principal.

ON DELETE CASCADE: si se elimina una fila del padre, se eliminarán todas las filas de la tabla secundaria que hacen referencia a la fila eliminada.
ON DELETE SET NULL: si se elimina una fila en el padre, todas las filas en la tabla secundaria hacen referencia a la fila eliminada se establecerán en NULL para las columnas de clave externa.
A diferencia de la restricción de clave principal, una tabla puede tener más de una restricción de clave externa.

Agregar una restricción de Foreign Key a una tabla

Si desea agregar una restricción de clave externa a una tabla existente, use la instrucción ALTER TABLE de la siguiente manera:

ALTER TABLE child_table 
ADD CONSTRAINT fk_name
FOREIGN KEY (col1,col2) REFERENCES parent_table(col1,col2);

Eliminar una restricción de Foreign Key

Para eliminar una restricción de clave externa, use la instrucción ALTER TABLE a continuación:

ALTER TABLE child_table
DROP CONSTRAINT fk_name;

Deshabilitar una restricción de Foreign Key

Para deshabilitar temporalmente una restricción externa, use la siguiente instrucción ALTER TABLE:

ALTER TABLE child_table
DISABLE CONSTRAINT fk_name;

Habilitar una restricción externa

De manera similar, también usa la instrucción ALTER TABLE para habilitar una restricción de clave externa deshabilitada:

ALTER TABLE child_table
ENABLE CONSTRAINT fk_name;

En este tutorial, ha aprendido a usar la restricción de clave externa de Oracle para hacer cumplir la relación entre tablas.