42 - Restricción "foreign key"

Hemos visto que una de las alternativas que SQLite ofrece para asegurar la integridad de datos es el uso de restricciones (constraints). Aprendimos que las restricciones se establecen en tablas y campos asegurando que los datos sean válidos y que las relaciones entre las tablas se mantengan.

Con la restricción "foreign key" se define un campo (o varios) cuyos valores coinciden con la clave primaria de la misma tabla o de otra, es decir, se define una referencia a un campo con una restricción "primary key" o "unique" de la misma tabla o de otra.

La integridad referencial asegura que se mantengan las referencias entre las claves primarias y las externas. Por ejemplo, controla que si se agrega un código de editorial en la tabla "libros", tal código exista en la tabla "editoriales".

También controla que no pueda eliminarse un registro de una tabla ni modificar la clave primaria si una clave externa hace referencia al registro. Por ejemplo, que no se pueda eliminar o modificar un código de "editoriales" si existen libros con dicho código.

Problema resuelto

Trabajamos con las tablas "libros" y "editoriales".
Eliminamos primero las tablas si ya existen:

drop table if exists libros;
drop table if exists editoriales;

Creamos las dos tablas y definimos la restricción "foreign key" en la tabla "libros":

create table editoriales(
	codigo integer primary key,
	nombre text
);

create table libros(
	codigo integer primary key,
	titulo text,
	autor text, 
	precio real,
	codigoeditorial integer	references editoriales(codigo)
);

Creamos una restricción "foreign key" para establecer el campo "codigoeditorial" como clave externa que haga referencia al campo "codigo" de "editoriales".

Cargamos tres registros en la tabla "editoriales":

 insert into editoriales(nombre) values('Planeta');
 insert into editoriales(nombre) values('Emece');
 insert into editoriales(nombre) values('Siglo XXI');

Cargamos una serie de registros en la tabla "libros" respetando la restricción impuesta en el campo "codigoeditorial":

 insert into libros (titulo, autor, codigoeditorial, precio)
  values('El aleph', 'Borges', 2, 34);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Antología poética', 'Borges', 1, 39.50);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Java en 10 minutos', 'Mario Molina', 1, 50.50);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Alicia en el pais de las maravillas', 'Lewis Carroll', 2, 19.90);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Martin Fierro', 'Jose Hernandez', 2, 25.90);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Martin Fierro', 'Jose Hernandez', 3, 16.80);

Las filas se agregan en forma correcta ya que en el campo "codigoeditorial" se ingresan siempre valores comprendidos entre 1 y 3, que son los códigos de editoriales existentes.

Intentemos de ingresar una fila con "codigoeditorial" que no exista en la tabla "editoriales":

 insert into libros(titulo, autor, codigoeditorial) 
  values('JSP basico','Tornado Luis',7);

Se produce un error ya que no existe el código 7 en la tabla "editoriales":

SQLite Browser foreign key

La restricción "foreign key" actúa en eliminaciones y actualizaciones. Si intentamos eliminar un registro o modificar un valor de clave primaria de una tabla si una clave foránea hace referencia a dicho registro, SQLite no lo permite. Por ejemplo, si intentamos eliminar una editorial a la que se hace referencia en "libros", aparece un mensaje de error y no se produce la eliminación:

delete from editoriales where codigo=1;

Se produce un error ya que en la tabla "libros" hay registros que hacen referencia a dicho código de la tabla "editoriales":

SQLite Browser foreign key

Podemos intentar de modificarlo y obtendremos el mismo error:

 update editoriales set codigo=100 where codigo=1;

Si hay uno o más registros en la tabla "libros" luego no podemos borrar la tabla "editoriales" debido a que hemos definido la restricción "foreign key":

 drop table if exists editoriales;

SQLite Browser foreign key

Problema propuesto

Una empresa tiene registrados sus clientes en una tabla llamada "clientes", también tiene una tabla "provincias" donde registra los nombres de las provincias.

  1. Borrar las tablas "clientes" y "provincias" si existen.

  2. Crear las tablas "clientes" y "provincias" definiendo una restricción "foreign key" en el campo "codigoprovincia" de la tabla "provincias":

     create table provincias(
  codigo integer primary key,
  nombre text
 );

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

  3. Inserte 3 filas en la tabla provincias:

     insert into provincias (nombre) values('Cordoba');
 insert into provincias (nombre) values('Santa Fe');
 insert into provincias (nombre) values('Corrientes');

  4. Ingresamos 7 filas en la tabla "clientes" que cumplan la restricción "foreign key" definida en el campo "codigoprovincia":

     insert into clientes(nombre,domicilio,ciudad,codigoprovincia) 
   values ('Lopez Marcos','Colon 111','Córdoba',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Perez Ana','San Martin 222','Cruz del Eje',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Garcia Juan','Rivadavia 333','Villa Maria',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Perez Luis','Sarmiento 444','Rosario',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Pereyra Lucas','San Martin 555','Cruz del Eje',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Gomez Ines','San Martin 666','Santa Fe',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Torres Fabiola','Alem 777','Ibera',3);

  5. Intentar ingresar una fila con un código de provincia inexistente.

  6. Intente eliminar el registro con código 3, de la tabla "provincias".

  7. Elimine el registro con código 4, de la tabla "provincias".

  8. Intente eliminar la tabla "provincias" habiendo registros en la tabla "clientes". Luego borre todos los registros de la tabla "clientes" y recién borre la tabla "provincias".

Solución 
drop table if exists clientes;
drop table if exists provincias;

 create table provincias(
  codigo integer primary key,
  nombre text
 );

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

 insert into provincias (nombre) values('Cordoba');
 insert into provincias (nombre) values('Santa Fe');
 insert into provincias (nombre) values('Corrientes');
 insert into provincias (nombre) values('Rio Negro'); 
 
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia) 
   values ('Lopez Marcos','Colon 111','Córdoba',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Perez Ana','San Martin 222','Cruz del Eje',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Garcia Juan','Rivadavia 333','Villa Maria',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Perez Luis','Sarmiento 444','Rosario',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Pereyra Lucas','San Martin 555','Cruz del Eje',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Gomez Ines','San Martin 666','Santa Fe',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Torres Fabiola','Alem 777','Ibera',3);

 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Rodriguez Pablo','Messtre Norte 334','Cosquin',10);

  delete from provincias where codigo=4;

  delete from clientes;
  delete from provincias;

Ejecución de ejercicios online

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

Resultado.....
Retornar