78 - Restricciones foreign key (acciones)


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", si existen:
 if object_id('clientes') is not null
  drop table clientes;
 if object_id('provincias') is not null
  drop table provincias;

2- Créelas con las siguientes estructuras:
 create table clientes (
  codigo int identity,
  nombre varchar(30),
  domicilio varchar(30),
  ciudad varchar(20),
  codigoprovincia tinyint,
  primary key(codigo)
 );

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

4- Establezca una restricción "foreign key" especificando la acción "en cascade" para 
actualizaciones y "no action" para eliminaciones.

5- Intente eliminar el registro con código 3, de "provincias".
No se puede porque hay registros en "clientes" al cual hace referencia y la opción para 
eliminaciones se estableció como "no action".

6- Modifique el registro con código 3, de "provincias".

7- Verifique que el cambio se realizó en cascada, es decir, que se modificó en la tabla "provincias" 
y en "clientes":
 select * from provincias;
 select * from clientes;

8- Intente modificar la restricción "foreign key" para que permita eliminación en cascada.
Mensaje de error, no se pueden modificar las restricciones.

9- Intente eliminar la tabla "provincias".
No se puede eliminar porque una restricción "foreign key" hace referencia a ella.
Ver solución

 if object_id('clientes') is not null
  drop table clientes;
 if object_id('provincias') is not null
  drop table provincias;

 create table clientes (
  codigo int identity,
  nombre varchar(30),
  domicilio varchar(30),
  ciudad varchar(20),
  codigoprovincia tinyint,
  primary key(codigo)
 );

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

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

 delete from provincias where codigo=3;

 update provincias set codigo=9 where codigo=3;

 select * from provincias;
 select * from clientes;

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

 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 si existen:
 if object_id('inscripciones') is not null
  drop table inscripciones;
 if object_id('deportes') is not null
  drop table deportes;
 if object_id('socios') is not null
  drop table socios;

2- Cree las tablas:
 create table deportes(
  codigo tinyint,
  nombre varchar(20),
  primary key(codigo)
 );

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

 create table inscripciones(
  documento char(8), 
  codigodeporte tinyint,
  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" que permita la actualización en cascada:
  alter table inscripciones
  add constraint FK_inscripciones_codigodeporte
  foreign key (codigodeporte)
  references deportes(codigo)
  on update cascade;

4- Establezca una restricción "foreign key" para "inscripciones" que haga referencia al campo 
"documento" de "socios" que permita la eliminación en cascada (Recuerde que se pueden establecer 
varias retricciones "foreign key" a una tabla):
  alter table inscripciones
  add constraint FK_inscripciones_documento
  foreign key (documento)
  references socios(documento)
  on delete cascade;

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:
 insert into inscripciones values('30000111',6,'s');
Mensaje de error.

7- Intente ingresar una inscripción con un documento inexistente en "socios":
 insert into inscripciones values('40111222',1,'s');
Mensaje de error.

8- Elimine un registro de "deportes" que no tenga inscriptos:
 delete from deportes where nombre='tenis';
Se elimina porque no hay inscriptos en dicho deporte.

9- Intente eliminar un deporte para los cuales haya inscriptos:
 delete from deportes where nombre='natacion';
No se puede porque al no especificarse acción para eliminaciones, por defecto es "no action" y hay 
inscriptos en dicho deporte.

10- Modifique el código de un deporte para los cuales haya inscriptos.
La opción para actualizaciones se estableció en cascada, se modifica el código en "deportes" y en 
"inscripciones".

11- Verifique los cambios:
 select * from deportes;
 select * from inscripciones;

12- Elimine el socio que esté inscripto en algún deporte.
Se elimina dicho socio de "socios" y la acción se extiende a la tabla "inscripciones".

13- Verifique que el socio eliminado ya no aparece en "inscripciones":
 select * from socios;
 select * from inscripciones;

14- Modifique el documento de un socio que esté inscripto.
No se puede porque la acción es "no action" para actualizaciones.

15- Intente eliminar la tabla "deportes":
 drop table deportes;
No se puede porque una restricción "foreign key" hace referencia a ella.

16- Vea las restricciones de la tabla "socios":
 exec sp_helpconstraint socios;
Muestra la restricción "primary key" y la referencia de una "foreign key" de la tabla 
"inscripciones".

17- Vea las restricciones de la tabla "deportes":
 exec sp_helpconstraint deportes;
Muestra la restricción "primary key" y la referencia de una "foreign key" de la tabla 
"inscripciones".

18- Vea las restricciones de la tabla "inscripciones":
 exec sp_helpconstraint inscripciones;
Muestra 3 restricciones. Una "primary key" y dos "foreign key", una para el campo "codigodeporte" 
que especifica "no action" en la columna "delete_action" y "cascade" en la columna "update_action"; 
la otra, para el campo "documento" especifica "cascade" en la columna "delete_action" y "no action" 
en "update_action".

Ver solución
 if object_id('inscripciones') is not null
  drop table inscripciones;
 if object_id('deportes') is not null
  drop table deportes;
 if object_id('socios') is not null
  drop table socios;

 create table deportes(
  codigo tinyint,
  nombre varchar(20),
  primary key(codigo)
 );

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

 create table inscripciones(
  documento char(8), 
  codigodeporte tinyint,
  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 update cascade;

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

 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';

 delete from deportes where nombre='natacion';

 update deportes set codigo=5 where nombre='natacion';

 select * from deportes;
 select * from inscripciones;

 delete from socios where documento='32222333'; 

 select * from socios;
 select * from inscripciones;

 update socios set documento='35555555' where documento='30000111';

 drop table deportes;

 exec sp_helpconstraint socios;

 exec sp_helpconstraint deportes;

 exec sp_helpconstraint inscripciones;

Retornar