Ejercicios propuestos : Restricciones foreign key (acciones)(Oracle)

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- Elimine las tablas "clientes" y "provincias":

 drop table clientes;
 drop table provincias;

2- Créelas con las siguientes estructuras:

 create table clientes (
  codigo number(5),
  nombre varchar2(30),
  domicilio varchar2(30),
  ciudad varchar2(20),
  codigoprovincia number(2),
  primary key(codigo)
 );

 create table provincias(
  codigo number(2),
  nombre varchar2(20),
  primary key (codigo)
 );

3- 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 values(100,'Perez Juan','San Martin 123','Carlos Paz',1);
 insert into clientes values(101,'Moreno Marcos','Colon 234','Rosario',2);
 insert into clientes values(102,'Acosta Ana','Avellaneda 333','Posadas',3);

4- Establezca una restricción "foreign key" especificando la acción "set null" para eliminaciones.

5- Elimine el registro con código 3, de "provincias" y consulte "clientes" para ver qué cambios ha realizado Oracle en los registros coincidentes
Todos los registros con "codigoprovincia" 3 han sido seteados a null.

6- Consulte el diccionario "user_constraints" para ver qué acción se ha establecido para las eliminaciones

7- Intente modificar el registro con código 2, de "provincias"

8- Elimine la restricción "foreign key" establecida sobre "clientes"

9- Establezca una restricción "foreign key" sobre "codigoprovincia" de "clientes" especificando la acción "cascade" para eliminaciones

10- Consulte el diccionario "user_constraints" para ver qué acción se ha establecido para las eliminaciones sobre las restricciones "foreign key" de la tabla "clientes"

11- Elimine el registro con código 2, de "provincias"

12- Verifique que el cambio se realizó en cascada, es decir, que se eliminó en la tabla "provincias" y todos los clientes de la provincia eliminada

13- Elimine la restricción "foreign key"

14- Establezca una restricción "foreign key" sin especificar acción para eliminaciones

15- Intente eliminar un registro de la tabla "provincias" cuyo código exista en "clientes"

16- Consulte el diccionario "user_constraints" para ver qué acción se ha establecido para las eliminaciones sobre la restricción "FK_CLIENTES_CODIGOPROVINCIA"

17- Intente elimimar la tabla "provincias"

18- Elimine la restricción "foreign key"

19- Elimine la tabla "provincias"

Ver solución

 drop table clientes;
 drop table provincias;

 create table clientes (
  codigo number(5),
  nombre varchar2(30),
  domicilio varchar2(30),
  ciudad varchar2(20),
  codigoprovincia number(2),
  primary key(codigo)
 );

 create table provincias(
  codigo number(2),
  nombre varchar2(20),
  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 values(100,'Perez Juan','San Martin 123','Carlos Paz',1);
 insert into clientes values(101,'Moreno Marcos','Colon 234','Rosario',2);
 insert into clientes values(102,'Acosta Ana','Avellaneda 333','Posadas',3);

 alter table clientes
 add constraint FK_clientes_codigoprovincia
  foreign key (codigoprovincia)
  references provincias(codigo)
  on delete set null;

 delete from provincias where codigo=3;
 select *from clientes;

 select constraint_name, constraint_type, delete_rule
  from user_constraints
  where table_name='CLIENTES';

 update provincias set codigo=9 where codigo=2;

 alter table clientes
  drop constraint FK_CLIENTES_CODIGOPROVINCIA;

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

 select constraint_name, delete_rule
  from user_constraints
  where table_name='CLIENTES' and
  constraint_type= 'R';

 delete from provincias where codigo=2;

 select *from provincias;
 select *from clientes;

 alter table clientes
  drop constraint FK_CLIENTES_CODIGOPROVINCIA;

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

 delete from provincias where codigo=1;

 select constraint_type, delete_rule
  from user_constraints
  where table_name='CLIENTES' and
  constraint_name='FK_CLIENTES_CODIGOPROVINCIA';

 drop table provincias;

 alter table clientes
  drop constraint FK_CLIENTES_CODIGOPROVINCIA;

 drop table provincias;

Segundo problema:

Un club dicta clases de distintos deportes. En una tabla llamada "deportes" guarda la información de los distintos deportes que se enseñan; en una tabla "socios", los datos de los socios y en una tabla "inscripciones" almacena la información necesaria para las inscripciones de los distintos socios a los distintos deportes.

1- Elimine las tablas:

 drop table inscripciones;
 drop table deportes;
 drop table socios;

2- Cree las tablas:

 create table deportes(
  codigo number(2),
  nombre varchar2(20),
  primary key(codigo)
 );

 create table socios(
  documento char(8),
  nombre varchar2(30),
  primary key(documento)
 );

 create table inscripciones(
  documento char(8), 
  codigodeporte number(2),
  matricula char(1),-- 's' si está paga, 'n' si no está paga
  primary key(documento,codigodeporte)
 );

3- Establezca una restricción "foreign key" para "inscripciones" que haga referencia al campo "codigo" de "deportes" indicando la acción "cascade" para eliminaciones

4- Establezca una restricción "foreign key" para "inscripciones" que haga referencia al campo "documento" de "socios" que permita la eliminación "set null" (Recuerde que se pueden establecer varias restricciones "foreign key" sobre una misma tabla)

5- Ingrese algunos registros en las tablas:

 insert into deportes values(1,'basquet');
 insert into deportes values(2,'futbol');
 insert into deportes values(3,'natacion');
 insert into deportes values(4,'tenis');

 insert into socios values('30000111','Juan Lopez');
 insert into socios values('31111222','Ana Garcia');
 insert into socios values('32222333','Mario Molina');
 insert into socios values('33333444','Julieta Herrero');

 insert into inscripciones values ('30000111',1,'s');
 insert into inscripciones values ('30000111',2,'s');
 insert into inscripciones values ('31111222',1,'s');
 insert into inscripciones values ('32222333',3,'n');

6- Intente ingresar una inscripción con un código de deporte inexistente:
Mensaje de error.

7- Intente ingresar una inscripción con un documento inexistente en "socios"

8- Elimine un registro de "deportes" que no tenga inscriptos.
Se elimina porque no hay inscriptos en dicho deporte.

9- Consulte "inscripciones", "deportes" y "socios" (realizando un join)

10-Elimine un deporte para los cuales haya inscriptos
Se elimina el deporte "natacion" de "deportes" y de "inscripciones", todos los registros con el valor correspondiente a "natacion" en "codigodeporte".

11- Verifique que se han eliminado los registros de "inscripciones" cuyo código de deporte correspondía a "natacion"

12- Intente eliminar el socio que esté inscripto en algún deporte.
Mensaje de error, porque la restricción "FK_inscripciones_documento" fue especificada "set null", pero el campo clave foránea "documento" en "inscripciones" no admite valores nulos (forma parte de la clave primaria).

13- Elimine la restricción "FK_inscripciones_documento" y créela nuevamente especificando la acción "cascade" para borrado

14- Elimine un socio que esté inscripto en algún deporte

15- Verifique que el socio fue eliminado de "socios" y de "inscripciones"

16- Consulte el diccionario "user_constraints" especificando las tres tablas y analice la información

Nos retorna la siguiente información:

TABLE_NAME	CONSTRAINT_NAME			CONSTRAINT_TYPE		DELETE_RULE 
------------------------------------------------------------------------------------ 
DEPORTES        SYS_C004914                     P 
INSCRIPCIONES   SYS_C004916                     P
INSCRIPCIONES   FK_INSCRIPCIONES_CODIGODEPORTE  R                       CASCADE     
INSCRIPCIONES   FK_INSCRIPCIONES_DOCUMENTO      R                       CASCADE     
SOCIOS          SYS_C004915                     P

17- Intente eliminar la tabla "deportes"

16- Elimine la restricción "foreign key" establecida sobre el campo "codigodeporte" de "inscripciones" y luego la tabla "deportes"

Ver solución

 drop table inscripciones;
 drop table deportes;
 drop table socios;

 create table deportes(
  codigo number(2),
  nombre varchar2(20),
  primary key(codigo)
 );

 create table socios(
  documento char(8),
  nombre varchar2(30),
  primary key(documento)
 );

 create table inscripciones(
  documento char(8), 
  codigodeporte number(2),
  matricula char(1),-- 's' si está paga, 'n' si no está paga
  primary key(documento,codigodeporte)
 );

  alter table inscripciones
  add constraint FK_inscripciones_codigodeporte
  foreign key (codigodeporte)
  references deportes(codigo)
  on delete cascade;

  alter table inscripciones
  add constraint FK_inscripciones_documento
  foreign key (documento)
  references socios(documento)
  on delete set null;

 insert into deportes values(1,'basquet');
 insert into deportes values(2,'futbol');
 insert into deportes values(3,'natacion');
 insert into deportes values(4,'tenis');

 insert into socios values('30000111','Juan Lopez');
 insert into socios values('31111222','Ana Garcia');
 insert into socios values('32222333','Mario Molina');
 insert into socios values('33333444','Julieta Herrero');

 insert into inscripciones values ('30000111',1,'s');
 insert into inscripciones values ('30000111',2,'s');
 insert into inscripciones values ('31111222',1,'s');
 insert into inscripciones values ('32222333',3,'n');

 insert into inscripciones values('30000111',6,'s');

 insert into inscripciones values('40111222',1,'s');

 delete from deportes where nombre='tenis';

 select s.nombre, s.documento, d.nombre as deporte
  from socios s
  join inscripciones i
  on s.documento=i.documento
  join deportes d
  on i.codigodeporte=d.codigo;

  delete from deportes where nombre='natacion';

 select *from inscripciones;
El registro de "inscripciones" con "documento" 32222333, ya no existe.

 delete from socios where documento='30000111'; 

 alter table inscripciones
  drop constraint FK_INSCRIPCIONES_DOCUMENTO;

 alter table inscripciones
  add constraint FK_inscripciones_documento
  foreign key (documento)
  references socios(documento)
  on delete cascade;

 delete from socios where documento='30000111';

 select *from socios where documento='30000111';
 select *from inscripciones where documento='30000111';

 select table_name,constraint_name, constraint_type, delete_rule
  from user_constraints
  where table_name='DEPORTES' OR
  table_name='SOCIOS' OR
  table_name='INSCRIPCIONES';

 drop table deportes;

 alter table inscripciones
  drop constraint FK_INSCRIPCIONES_CODIGODEPORTE;

 drop table deportes;

62 - Restricciones foreign key (acciones)

Retornar