PostgreSQL: clave externa

En este artículo, analizaremos las restricciones de clave externa de PostgreSQL mediante declaraciones SQL. Una clave externa es una columna o un grupo de columnas que se utiliza para identificar una fila de forma única de una tabla diferente. La tabla que comprende la clave externa se denomina tabla de referencia o tabla secundaria. Y la tabla a la que hace referencia la clave externa se conoce como tabla referenciada o tabla principal. Una tabla puede poseer varias claves foráneas según sus relaciones con otras tablas. 

Syntax:  FOREIGN KEY (column) REFERENCES parent_table (table_name)

Analicemos la sintaxis anterior:

• RESTRICCIÓNRESTRICCIÓN
• CLAVE EXTERNA
• REFERENCIAS
• EN ACTUALIZACIÓN DE ELIMINACIÓN

EN ACTUALIZAR acción EN ELIMINAR

clientescontactos

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
);

En este ejemplo, la tabla de clientes es la tabla principal y la tabla de contactos es la tabla secundaria. Cada cliente tiene cero o muchos contactos y cada contacto pertenece a cero o un cliente. La columna customer_id en la tabla de contactos es la columna de clave externa que hace referencia a la columna de clave principal con el mismo nombre en la tabla del cliente. La siguiente restricción de clave externa fk_customer en la tabla de contactos define el ID_cliente como la clave externa:

CONSTRAINT fk_customer
   FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)

AL ELIMINAR ACTUALIZAR SIN ACCIÓN

clientescontactos

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

clientes

DELETE FROM customers
WHERE customer_id = 1;

ON DELETE NO ACTIONcontacts

ERROR:  update or delete on table "customers" violates foreign key constraint
        "fk_customer" on table "contacts"
DETAIL:  Key (customer_id)=(1) is still referenced from table "contacts".
SQL state: 23503

RESTRINGIRSIN ACCIÓNAPLAZABLEINICIALMENTE DIFERIDOINICIALMENTE INMEDIATO

ON DELETE CASCADE establece automáticamente NULL en las columnas de clave externa en las filas de referencia de la tabla secundaria cuando se eliminan las filas a las que se hace referencia en la tabla principal. Las siguientes declaraciones eliminan las tablas de muestra y las vuelven a crear con la clave externa que usa la acción SET NULL en la cláusula ON DELETE:

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE SET NULL
);

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

clientescontactos

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

SET NULLclientes

DELETE FROM customers
WHERE customer_id = 1;

ON DELETE SET NULLcontactoscontactos

SELECT * FROM contacts;

Dará como resultado lo siguiente:

id_cliente id_cliente NULL

CASCADA

ON DELETE CASCADE elimina automáticamente todas las filas de referencia en la tabla secundaria cuando se eliminan las filas a las que se hace referencia en la tabla principal. En la práctica, ON DELETE CASCADE es la opción más utilizada. Las siguientes declaraciones recrean las tablas de ejemplo. Sin embargo, la acción de eliminación de fk_customer cambia a CASCADE:

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE CASCADE
);

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

DELETE FROM customers
WHERE customer_id = 1;

ON DELETE CASCADEcontactos

SELECT * FROM contacts;

Esto dará como resultado lo siguiente:

ESTABLECER PREDETERMINADO

ON ELIMINAR ESTABLECER POR DEFECTO ALTERAR TABLA

ALTER TABLE child_table 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (fk_columns) 
REFERENCES parent_table (parent_key_columns);

Cuando agrega una restricción de clave externa con la opción ON DELETE CASCADE a una tabla existente, debe seguir estos pasos:

Primero, elimine las restricciones de clave externa existentes:

ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;

EN ELIMINAR CASCADA

ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;