53 - Restricciones (foreign key)


Primer problema:
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- Cree las tablas "clientes" y "provincias":

 create table clientes (
  codigo serial,
  nombre varchar(30),
  domicilio varchar(30),
  ciudad varchar(20),
  codigoprovincia smallint,
  primary key(codigo)
 );

 create table provincias(
  codigo serial,
  nombre varchar(20),
  primary key(codigo)
 );

En este ejemplo, el campo "codigoprovincia" de "clientes" es una clave foránea, se emplea para 
enlazar la tabla "clientes" con "provincias".

2- Intente agregar una restricción "foreign key" a la tabla "clientes" que haga referencia al campo 
"codigo" de "provincias" (No se puede porque "provincias" no tiene restricción "primary key" "unique")

3- Establezca una restricción "primary key" al campo "codigo" de "provincias"

4- Ingrese algunos registros para ambas tablas:
 insert into provincias values(1,'Cordoba');
 insert into provincias values(2,'Santa Fe');
 insert into provincias values(3,'Misiones');
 insert into provincias values(4,'Rio Negro');

 insert into clientes(nombre,domicilio,ciudad,codigoprovincia) 
  values('Perez Juan','San Martin 123','Carlos Paz',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values('Moreno Marcos','Colon 234','Rosario',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values('Acosta Ana','Avellaneda 333','Posadas',3);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values('Luisa Lopez','Juarez 555','La Plata',6);

5- Intente agregar la restricción "foreign key" del punto 2 a la tabla "clientes"
No se puede porque hay un registro en "clientes" cuyo valor de "codigoprovincia" no existe en 
"provincias".

6- Elimine el registro de "clientes" que no cumple con la restricción y establezca la restricción 
nuevamente.

7- Intente agregar un cliente con un código de provincia inexistente en "provincias".
No se puede.

8- Intente eliminar el registro con código 3, de "provincias".
No se puede porque hay registros en "clientes" al cual hace referencia.

9- Elimine el registro con código "4" de "provincias".
Se permite porque en "clientes" ningún registro hace referencia a él.

10- Intente modificar el registro con código 1, de "provincias".
No se puede porque hay registros en "clientes" al cual hace referencia.
Ver solución

 drop table if exists clientes;
 drop table if exists provincias;

 create table clientes (
  codigo serial,
  nombre varchar(30),
  domicilio varchar(30),
  ciudad varchar(20),
  codigoprovincia smallint,
  primary key(codigo)
 );

 create table provincias(
  codigo serial,
  nombre varchar(20)
 );

 alter table clientes
 add constraint FK_clientes_codigoprovincia
  foreign key (codigoprovincia)
  references provincias(codigo);

 alter table provincias
 add constraint PK_provincias_codigo
  primary key (codigo);

 insert into provincias values(1,'Cordoba');
 insert into provincias values(2,'Santa Fe');
 insert into provincias values(3,'Misiones');
 insert into provincias values(4,'Rio Negro');

 insert into clientes(nombre,domicilio,ciudad,codigoprovincia) 
  values('Perez Juan','San Martin 123','Carlos Paz',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values('Moreno Marcos','Colon 234','Rosario',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values('Acosta Ana','Avellaneda 333','Posadas',3);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values('Luisa Lopez','Juarez 555','La Plata',6);

 alter table clientes
 add constraint FK_clientes_codigoprovincia
  foreign key (codigoprovincia)
  references provincias(codigo);

 delete from clientes where codigoprovincia=6;
 alter table clientes
 add constraint FK_clientes_codigoprovincia
  foreign key (codigoprovincia)
  references provincias(codigo);

 insert into clientes(nombre,domicilio,ciudad,codigoprovincia) 
  values('Garcia Marcos','Colon 877','Lules',9);

 delete from provincias where codigo=3;

 delete from provincias where codigo=4;

 update provincias set codigo=7 where codigo=1;

 


Retornar