43 - Restricción "foreign key" en la misma tabla

La restricción "foreign key", que define una referencia a un campo con una restricción "primary key" o "unique" se puede definir entre distintas tablas (como hemos aprendido) o dentro de la misma tabla.

Veamos un ejemplo en el cual definimos esta restricción dentro de la misma tabla.

Problema resuelto

Una mutual almacena los datos de sus afiliados en una tabla llamada "afiliados". Algunos afiliados inscriben a sus familiares. La tabla contiene un campo que hace referencia al afiliado que lo incorporó a la mutual, del cual dependen.

Eliminamos la tabla si existe:

drop table if exists afiliados;

Creamos la tabla y definimos la restricción "foreign key":

 create table afiliados(
  numero integer primary key,
  documento text,
  nombre text,
  afiliadotitular integer references afiliados(numero)
 );

En caso que un afiliado no haya sido incorporado a la mutual por otro afiliado, el campo "afiliadotitular" almacenará "null".

Luego de aplicar esta restricción, cada vez que se ingrese un valor en el campo "afiliadotitular", SQLite controlará que dicho número exista en la tabla, si no existe, mostrará un mensaje de error.

Ingresemos una filas con valores correctos en el campo afiliadotitular:

 insert into afiliados(documento,nombre,afiliadotitular) values('22222222','Perez Juan',null);
 insert into afiliados(documento,nombre,afiliadotitular) values('23333333','Garcia Maria',null);
 insert into afiliados(documento,nombre,afiliadotitular) values('24444444','Lopez Susana',null);
 insert into afiliados(documento,nombre,afiliadotitular) values('30000000','Perez Marcela',1);
 insert into afiliados(documento,nombre,afiliadotitular) values('31111111','Morales Luis',1);
 insert into afiliados(documento,nombre,afiliadotitular) values('32222222','Garcia Maria',2);

Intentemos ahora ingresar un afiliado con valor incorrecto en el campo 'afiliadotitular':

 insert into afiliados(documento,nombre,afiliadotitular) values('77777777','Rodriguez Pablo',100);

SQLite Browser foreign key

Si intentamos eliminar un afiliado que es titular de otros afiliados, no se podrá hacer:

delete from afiliados where numero=1;

Problema propuesto

Una empresa registra los datos de sus clientes en una tabla llamada "clientes". Dicha tabla contiene un campo que hace referencia al cliente que lo recomendó denominado "referenciadopor". Si un cliente no ha sido referenciado por ningún otro cliente, tal campo almacena "null".

  1. Borrar la tabla "clientes" si existe

  2. Crear la tabla "clientes" definiendo la restricción "foreign key":

     create table clientes(
  codigo integer primary key,
  nombre text,
  domicilio text,
  ciudad text,
  referenciadopor integer references clientes(codigo)
 );

  3. Almacenar una serie de registros con valores correctos:

     insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values (50,'Juan Perez','Sucre 123','Cordoba',null);
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(90,'Marta Juarez','Colon 345','Carlos Paz',null);
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(110,'Fabian Torres','San Martin 987','Cordoba',50);
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(125,'Susana Garcia','Colon 122','Carlos Paz',90);

  4. Intente agregar un registro que infrinja la restricción "foreign key".

  5. Intente modificar el código de un cliente que está referenciado en "referenciadopor"

  6. Intente eliminar un cliente que sea referenciado por otro en "referenciadopor"

  7. Cambie el valor de código de un cliente que no referenció a nadie.

  8. Elimine un cliente que no haya referenciado a otros.

Solución 
 drop table if exists clientes;
 
 create table clientes(
  codigo integer primary key,
  nombre text,
  domicilio text,
  ciudad text,
  referenciadopor integer references clientes(codigo)
 );
 
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values (50,'Juan Perez','Sucre 123','Cordoba',null);
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(90,'Marta Juarez','Colon 345','Carlos Paz',null);
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(110,'Fabian Torres','San Martin 987','Cordoba',50);
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(125,'Susana Garcia','Colon 122','Carlos Paz',90);
   
 insert into clientes(codigo, nombre, domicilio, ciudad, referenciadopor)
   values(140,'Ana Herrero','Colon 890','Carlos Paz',1500);
 
 update clientes set codigo=200 where codigo=50

 delete from clientes where codigo=50;
 
 update clientes set codigo=200 where codigo=125;
 
 delete from clientes where codigo=200;

Ejecución de ejercicios online

Puede ejecutar comandos de SQLite directamente en el sitio sin tener que instalar nada en su computadora.

Resultado.....
Retornar