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;