Restricción de clave externa en SQL

Foreign Key es una columna que hace referencia a la clave principal/clave única de otra tabla. Por lo tanto, demuestra la relación entre las tablas y actúa como referencia cruzada entre ellas. La tabla en la que se define la clave foránea se denomina tabla foránea/tabla de referencia. La tabla que define la clave principal/única y está referenciada por una clave externa se denomina tabla principal/tabla maestra/tabla referenciada. Se define en la instrucción Crear tabla/Alterar tabla.

Para la tabla que contiene la clave externa, debe coincidir con la clave principal en la tabla a la que se hace referencia para cada fila. Esto se llama Integridad Referencial. La clave externa garantiza la integridad referencial.

Propiedades :

• El padre al que se hace referencia tiene que ser clave única/principal.
• El niño puede tener duplicados y valores nulos.
• El registro principal se puede eliminar si no existe un hijo.
• La tabla principal no se puede actualizar si existe un elemento secundario.
• Debe hacer referencia a PRIMARY KEY en la tabla principal.
• La columna de clave externa y la columna de restricción deben tener tipos de datos coincidentes.
• Los registros no se pueden insertar en la tabla secundaria si el registro correspondiente en la tabla principal no existe.
• Los registros de la tabla maestra no se pueden eliminar si los registros correspondientes en la tabla secundaria salen.

1. Clave externa de SQL a nivel de columna:

   Sintaxis –

   Create table people (no int references person, 
                             Fname varchar2(20));
                   OR
   Create table people (no int references person(id), 
                                 Fname varchar2(20));            
   

   Aquí la tabla Person debe tener una clave principal con tipo int. Si hay una clave principal de una sola columna en la tabla, se puede omitir el nombre de la columna en la sintaxis. Entonces, la sintaxis anterior funciona correctamente.

   Para comprobar la restricción,

   • Si la tabla principal no tiene clave principal.

     OUTPUT : 
     Error at line 1 : referenced table does not have a primary key.
     

   • Si la tabla principal tiene una clave principal de un tipo de datos diferente.

     OUTPUT : 
     Error at line 1 : column type incompatible with referenced column type.
     

2. Clave foránea de SQL a nivel de tabla:

   Sintaxis –

   create table people(no varchar2(10), 
                        fname varchar2(20), 
                        foreign key(no) references person);
                           OR
   create table people(no varchar2(10), 
                        fname varchar2(20), 
                        foreign key(no) references person(id));
   

   El nombre de la columna de la tabla a la que se hace referencia se puede ignorar.

3. Insertar operación en la tabla de clave externa:
   si el valor correspondiente en la tabla externa no existe, no se puede insertar un registro en la tabla secundaria.

   OUTPUT : 
   Error at line 1 : integrity constraint violated - parent key not found.
   

4. Operación de eliminación en la tabla de clave externa:
   cuando se elimina un registro en la tabla principal y existe el registro correspondiente en la tabla secundaria, se muestra un mensaje de error que impide que se lleve a cabo la operación de eliminación.

   OUTPUT : 
   Error at line 1 : integrity constraint violated - child record found.
   

5. Clave externa con ON DELETE CASCADE:
   El comportamiento predeterminado de la clave externa se puede cambiar usando ON DELETE CASCADE. Cuando se especifica esta opción en la definición de clave externa, si se elimina un registro en la tabla maestra, se eliminarán todos los registros correspondientes en la tabla de detalles.

   Sintaxis –

   create table people(no varchar2(10), 
                      fname varchar2(20), 
                      foreign key(no) 
   references person on delete cascade);
   

   Ahora, al eliminar registros de una persona, se eliminarán todos los registros correspondientes de la tabla secundaria.

   OUTPUT :
   select * from person;
   no rows selected 
   
   select * from people;
   no rows selected 

6. Clave externa con ON DELETE SET NULL:
   una clave externa con SET NULL ON DELETE significa que si se elimina el registro en la tabla principal, los registros correspondientes en la tabla secundaria tendrán campos de clave externa establecidos en nulo. Los registros en la tabla secundaria no se eliminarán.

   Sintaxis –

   create table people(no varchar2(10), 
                    fname varchar2(20), 
                       foreign key(no)
   references person on delete set null);
   

   OUTPUT :
   select * from person;
   no rows selected
   
   select * from people;
   NO Fname
   pqr 
   

   Observe el campo «No» en la tabla de personas que hacía referencia a la clave principal de la tabla de personas. Al eliminar los datos de la persona, se establecerá como nulo en la tabla secundaria de personas. Pero el registro no se borrará.