PDF de programación - EJEMPLOS PRÁCTICOS SQL - INTEGRIDAD REFERENCIAL

EJEMPLOS PRÁCTICOS SQL

EJEMPLOS PRÁCTICOS SQL

INTEGRIDAD REFERENCIAL
INTEGRIDAD REFERENCIAL

DROP SCHEMA IF EXISTS Tablas1;
CREATE SCHEMA Tablas1;
USE Tablas1;

create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(10),
),

p

(

PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(
create table Pedidos(

npedido INTEGER,
fecha DATE,
Cantidad DOUBLE,
cliente_Dni VARCHAR(10),
PRIMARY KEY (
did )
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni)

)ENGINE=InnoDB;
)
;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO Pedidos VALUES ('122' '2010/01/05' 7 '7211545v');
INSERT INTO Pedidos VALUES ( 122 , 2010/01/05 ,7, 7211545v );

TABLA: Cliente

TABLA: Pedidos

Esto es un ejemplo de intento de borrado de una tupla que tiene un campo que
aparece como clave foránea en otra tabla. Por omision se aplica la condición
de restrict y no se puede borrar hasta que no borre la tupla de la tabla hija.

Esto es un ejemplo de intento de actualización de una tupla que tiene un campo que
aparece como clave foránea en otra tabla. Por omision se aplica la condición
de restrict y no se puede actualizar.
de restrict y no se puede actualizar.

DROP SCHEMA IF EXISTS Tablas2;
CREATE SCHEMA Tablas2;
USE Tablas2;

create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(10),

PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER,
fecha DATE,
Cantidad DOUBLE,
cliente Dni VARCHAR(10)
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

TABLA: Cliente

TABLA: Pedidos

p

Para probar la restricción ON UPDATE CASCADE … actualizamos el valor
de un DNI. El resultado debe de ser que se actualiza la tabla padre y la hija

Se observa que se han actualizado las tablas padre e hija

Si ahora borramos una tupla de la tabla padre se borra la tupla
correspondiente de la tabla hija.

DROP SCHEMA IF EXISTS Tablas3;
CREATE SCHEMA Tablas3;
USE Tablas3;

create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(10),

PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER,
fecha DATE,
Cantidad DOUBLE,
cliente Dni VARCHAR(10)
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE I
DB
)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

La restricción más adecuada en la mayoría de los casos es
evitar realizar borrados en cascada y sin embargo si
evitar realizar borrados en cascada y sin embargo si
actualizar en cascada

Observamos que la actualización en cascada ha funcionado

A diferencia del ejemplo de la Tablas2 ahora no se puede
borrar una tupla de la tabla padre por la restricción ON DELETE RESTRICT.

Nótese una cuestión que a veces es causa de confusión.
Aunque es en la tabla hija donde se escribe la restricción de la clave foránea
Aunque es en la tabla hija donde se escribe la restricción de la clave foránea,
es decir, que un atributo depende de la clave de otra tabla….

Se puede borrar sin ningún problema una tupla de una tabla que contiene
una clave foránea sin afectar a la tupla correspondiente de la tabla padre…
En la figura se ve el ejemplo de borrar una fila en la tabla de pedidos.

Como se ve en la figura la tabla de pedidos está vacía y
la tabla de clientes está intacta
la tabla de clientes está intacta.

DROP SCHEMA IF EXISTS Tablas4;
CREATE SCHEMA Tablas4;
USE Tablas4;

create table Cliente(

Dni VARCHAR(10),
Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(10),

PRIMARY KEY (Dni)
)ENGINE=InnoDB;
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER,
fecha DATE,
Cantidad DOUBLE,
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE

( p

CASCADE

)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO P did VALUES ('122' '2010/01/0 '
' 211 4 ')
INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

t

A í
Aquí mostramos el ejemplo de intentar incorporar una fila nueva de
pedidos de un cliente que no existe en la tabla de clientes. Las
reglas de integridad referencial nos lo impiden

l d i t

fil

l

j

t

i

d

Insertamos ahora dos nuevos clientes……..

El resultado lo observamos en la figura ……

Ahora si que podemos incorporar una tupla nueva de pedido con
el cliente incorporado en la tabla de clientes

EJEMPLOS PRÁCTICOS SQL

MODIFICACIÓN TABLAS
MODIFICACIÓN TABLAS

DROP SCHEMA IF EXISTS T bl
5
DROP SCHEMA IF EXISTS Tablas5;
CREATE SCHEMA Tablas5;
USE Tablas5;

create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(10),

PRIMARY KEY (Dni)
PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER
npedido INTEGER,
fecha DATE,
Cantidad DOUBLE,
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO Pedidos VALUES ('122' '2010/01/05' 7 '7211545v');
INSERT INTO Pedidos VALUES ( 122 , 2010/01/05 ,7, 7211545v );

Agregamos una columna a la tabla con ALTER …ADD

Cambiamos el nombre de una columna a la tabla con ALTER …CHANGE

Modificamos el tipo de datos de una columna a la tabla con ALTER …MODIFY

Eliminamos una columna a la tabla con ALTER …DROP

Estamos tratando de borrar una tabla padre. Las restricciones de la BD
no nos permite al tener asociada la tabla una clave ajena

La tabla hija “pedidos” se puede borrar sin problemas.
Es donde se haya la clave ajena
Es donde se haya la clave ajena

EJEMPLOS PRÁCTICOS SQL

INSERCIÓN DATOS EN TABLAS
INSERCIÓN DATOS EN TABLAS

DROP SCHEMA IF EXISTS Tablas6;
CREATE SCHEMA Tablas6;
USE Tablas6;

create table Cliente(
create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(40),
PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER,
fecha DATE,
ec a
,
Cantidad DOUBLE,
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE

RESTRICT ON UPDATE CASCADE
RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB;

create table Personal(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(40),
PRIMARY KEY (Dni)
)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');
INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');
INSERT INTO Personal VALUES ('7211541v','Juan','Garzón Rodriguez');
INSERT INTO Personal VALUES ('7211542v','Antonio','Marina Esquivel');
INSERT INTO Personal VALUES ('7211543v' 'Cesar' 'Bernal SanJose');
INSERT INTO Personal VALUES ( 7211543v , Cesar , Bernal SanJose );
INSERT INTO Personal VALUES ('7211544v','Rodrigo','Alonso Vera');
INSERT INTO Personal VALUES ('7211546v','Maria','Lopez Gomez');

TABLA: Cliente

TABLA: Pedidos

TABLA: Personal

Con este comando que es un mezcla de INSERT INTO y SELECT conseguimos
meter en la tabla cliente 5 tuplas

EJEMPLOS PRÁCTICOS SQL

ACTUALIZACIÓN DATOS EN TABLAS
ACTUALIZACIÓN DATOS EN TABLAS

DROP SCHEMA IF EXISTS Tablas7;
CREATE SCHEMA Tablas7;
USE Tablas7;

create table Cliente(
create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(40),
PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER,
fecha DATE,
ec a
,
Cantidad DOUBLE,
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON

DELETE RESTRICT ON UPDATE CASCADE
DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB;

create table Personal(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(40),
PRIMARY KEY (Dni)
)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');
INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

INSERT INTO Cliente VALUES ('7211541v','Juan','Garzón Rodriguez');
INSERT INTO Cliente VALUES ('7211542v' 'Antonio' 'Marina Esquivel');
INSERT INTO Cliente VALUES ( 7211542v , Antonio , Marina Esquivel );
INSERT INTO Cliente VALUES ('7211543v','Cesar','Bernal SanJose');
INSERT INTO Cliente VALUES ('7211544v','Maria','Alonso Vera');
INSERT INTO Cliente VALUES ('7211546v','Maria','Lopez Gomez');

Vamos a probar el comando actualizar de SQL.
Para ello actualizamos todas las tuplas en la que aparece Maria

En este ejemplo vemos como es imposible actualizar los dos registros
en los que aparece en nombre el valor Carmen a un mismo DNI
DNI
ya que DNI tiene la restricción de clave primaria

l C

i

l

b

l

EJEMPLOS PRÁCTICOS SQL

CONSULTAS BÁSICAS CON SELECT
CONSULTAS BÁSICAS CON SELECT

DROP SCHEMA IF EXISTS SELECT1;
DROP SCHEMA IF EXISTS SELECT1;
CREATE SCHEMA SELECT1;
USE SELECT1;

create table Información Ventas(
create table Información_Ventas(
Tienda VARCHAR(10),
Ventas INTEGER,
Fecha DATE
)ENGINE=InnoDB;
)
;

create table Zona_Ventas(

g

Región VARCHAR(10),
),
Tienda VARCHAR(10)
)ENGINE=InnoDB;

(

INSERT INTO Información Ventas VALUES ('Madrid',1500,'2010/01/05');
);
INSERT INTO Información_Ventas VALUES ('Sevilla',250,'2010/01/08');
INSERT INTO Información_Ventas VALUES ('Madrid',300,'2010/01/07');
INSERT INTO Información_Ventas VALUES ('Barcelona',700,'2010/01/08');

_

(

,

,

INSERT INTO Zona_Ventas VALUES ('Centro-Sur','Madrid');
INSERT INTO Zona_Ventas VALUES ('Centro-Sur','Sevilla');
INSERT INTO Zona Ventas VALUES ('Noreste','Zaragoza');
);
INSERT INTO Zona_Ventas VALUES ('Noreste','Barcelona');

g

_

(

,

TABLA: Informacion_ventas

TABLA:Zona_Ventas

Tabla: Información_Ventas

Selección Básica:

Seleccionamos el nombre de todas las tiendas

Tabla: Información_Ventas

D