Desde hace dos clase hemos estado trabajando con las restricciones sobre nuestras tablas. Dichas restricciones fueron especificamente las llaves primarias y foráneas, las cuales son de enorme importancia para maneter la integridad de los datos en nuestra BD; asimismo, las restricciones que veremos en esta clase son útiles para maneter dicha integridad.
Autor: Luis Fernando Apáez Álvarez
CHECK¶Comencemos por ver los primeros registros de la tabla empleados:
y supongamos, por ejemplo, que por políticas de la empresa ningún empleado puede ganar más de 65000 mensuales. Así como hemos construido nuestra tabla no tendríamos problema alguno en ingresar un empleado cuyo salario exceda esta cifra. Por ello es necesario la implementación de una restricción sobre esta columna para impedir que se ingresen valores superiores a los 65000, lo cual conseguiremos al definir la restricción del tipo CHECK sobre al columna salario, de modo que con esta retricción logramos controlar los valores que se ingresan en una columna en especifico.
Para agregar dicha restricción escribimos:
ALTER TABLE <<nombre_tabla>> ADD CONSTRAINT ck_<<nombre_tabla>>_<<nombre_columna>> CHECK (<<condicion>>)
donde notamos que en el CHECK debemos definir alguna condición en especifico. En nuestro ejemplo, la condición sería que salario < 65000. Por consiguiente, definimos el siguiente CHECK sobre la columna salario:
ALTER TABLE empleados ADD CONSTRAINT ck_empleados_salario CHECK(salario < 65000);
cuando lo ejecutemos probablemente nos salga un error que nos diga que dicha restricción es violada por algunos registros en nuestra tabla, es decir, ya existen empleados que ganan más de 65000. En mi caso sólo hay un empleado y éste es:
SELECT * FROM empleados WHERE salario > 65000;
cuyo salario es exorbitante. Recordemos que los datos los generamos de amnera aleatoria, por lo que casos como estos se nos presentan. Se puede solucionar fácilmente este problema si actualizamos el valor del salario para este empleado:
UPDATE empleados SET salario = 60000 WHERE id_empleados = 80;
nuevamente ejecutamos el código para definir el CHECK y ahora no deberíamos tener problemas. Ahora bien, ya hemos definido la restricción CHECK sobre la columna salarios, de esta forma, si intentaramos ingresar un empleado nuevo o actualizar los datos de un empleado y colocar en la columna salario un valor mayor a los 65000 obtendríamos seguramente un error. Comprebemos lo dicho anteriromente
INSERT INTO empleados(nombre, app, apm, puesto, salario, horario)
VALUES('Jenny', 'López', 'López', 'Vendedor', 70000, '7:00-10:00');
con lo que obtenemos
un error. Así, estamos garantizando que los registros de la tabla empleados, en especifico los valores de la columna salario, mantenga una integridad de acuerdo a nuestra necesidades. Otro ejemplo de la restricción CHECK es en campos referentes al sexo. Supongamos que en la tabla empleados tenemos una columna destinada al sexo del empleado; para esta columna podríamos definir una restricción CHECK de la siguiente forma
ALTER TABLE empleados ADD CONSTRAINT ck_empleados_sexo
CHECK(sexo IN ('Masculino', 'Femenino', 'Otro'))
donde sólo estamos premitiendo que los valores de la tabla empleados en la columna sexo sean Masculino, Femenino u Otros. Así, si llegaramos a ingresar hombre, entonces se nos arrojaría un error.
Otro ejemplo común es agregar una restricción CHECK sobre columnas de números telefónico. Por ejemplo, supongamos que tenemos la columna num_tel en la tabla empleados y deseamos que no sean ingresados números telefónicos con más de 10 cifras, o que lleguen a ingresarse números telefónicos con alguna letra. Entonces emplearemos la siguiente restricción CHECK sobre la columna num_tel
ALTER TABLE empleados ADD CONSTRAINT ck_empleados_num_tel
CHECK(num_tel ~ '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
donde especificamos que el número telefónico debe ser de 10 cifras y cada una de estas cifras será un entero entre el 0 y el 9, además es necesario colocar el símbolo ~ después del nombre de la columna a la cual le aplicares la restricción CHECK. También podemos utilizar intervalos de letras en vez de numéricos (enteros), por ejemplo '[A-Z][A-Z]' indicaría que se debe escribir una palabra conformada de dos letras mayúsculas entre la A y la Z.
Ahora bien, podemos eliminar restricciones CHECK; por ejemplo eliminemos la restricción ck_salario de la tabla empleados
ALTER TABLE empleados DROP CONSTRAINT ck_empleados_salario;
es decir, la sintaxis general para eliminar una restricción CHECK es
ALTER TABLE <<nombre_tabla>> DROP CONSTRAINT ck_<<nombre_tabla>>_<<nombre_columna>>;
NOT NULL¶Hay veces en las que no podemos permitir valores nulos dentro de una columna. Por ejemplo, en la tabla empleados, en la columna puesto debemos especificar para cada uno de los empleados el puesto que éstos llevarán a cabo. Así, de manera general, la restricción NOT NULL nos sirve para forzar el ingreso de datos para ciertas columnas.
Para definir esta restricción sobre la columna puesto escribimos
ALTER TABLE empleados ALTER COLUMN puesto SET NOT NULL;
de tal manera, si nosotros intentamos registrar a un empleado nuevo y omitimos el valor correspondiente a la columna puesto, entonces postgres nos arrojará un error:
INSERT INTO empleados(nombre, app, apm, salario, horario)
VALUES('Jenny', 'López', 'López', 10000, '6:00-9:00');
unique¶Como vimos en la restricción de llave primaria al definirla sobre una columna, dentro de esta columna no se aceptarán valores repetidos. Es decir, si definimos la llave primaria, por ejemplo, sobre el id_empleados de la tabla empleados, entonces no se pirmitirán id's repetidos.
La restricción unique actúa de manera muy similar, pues al definir ésta sobre alguna columna, no se permitirán valores duplicados. Sin embargo, dicha columna no tiene la características principales para ser considerada como llave primaria dentro de esta tabla y además ésta permite valores nulos (lo cual la llave primaria no permite). Por ejemplo, si en la tabla empleados tuvieramos una columna denominada curp, entonces sería necesario definir la restricción unique para dicha columna, lo cual conseguiríamos escribiendo
ALTER TABLE empleados ADD CONSTRAINT uq_empleados_curp UNIQUE(curp);
Observación: El proceso que estamos siguiendo para definir las restricciones no es el mejor, si bien es ilustrativo, éste debe realizarse desde que definimos las tablas y no después como lo estamos haciendo ahora. De tal manera, lo primero que se debe hacer en un proyecto es pensar las tablas que se crearán, pensar las columnas de cada tabla y definir las restricciones sobre las columnas que sean necesarias.
Por ende, la forma en la que estamos llevando el curso es, primero, aprender el código SQL y, después, sobre la marcha ir modificando el proyecto, lo cual es bastante ilustrativo y práctico; sin embargo no es la forma correcta de trabajar, pero, una vez que finalices el curso tendrás de igual manera las herramientas que te peritan trabajar con proyectos de la mejor forma posibble.
Dentro de la tabla productos tenemos distintos productos que vendemos en la papelería. Use el siguiente código
SELECT DISTINCT nombre_productos FROM productos;
para conocer todos los productos que se venden en la papelería.
a. Define una restricción CHECK sobre la columna nombre_productos de la tabla productos, basándote en la información que obtuviste de la consulta anterior.
b. Intenta ingresar un registro que viole la restricción creada en el punto anterior.
c. Elimina la restricción creada en el punto a.
Elige cualquiera de tus tablas y coloca una restricción de no nulidad en alguna de sus columnas, así como una restricción UNIQUE
Hasta el momento, al menos en las tres tablas que trabajamos en clase (empleados, ventas y productos) ya hemos definidos las principales restricciones: las llaves primarias y foráneas. Con lo anterior garantizamos la integridad de nuestros datos y evitamos errores a la hora de ingresar información en cada una de ellas.
Ahora bien, para trabajar de la mejor forma en clases posteriores ingresaremos bastantes filas a nuestras tres tablas base (las que mencionamos antes), lo que nos permitirá introducirnos a manejar bases de datos con muchos registros. Realizaremos pues 1000 inserciones en las tablas empleados, ventas y productos, pero no ingresaremos de forma manual todas esta información, nos auxiliaremos de la página mockaroo para generar las 1000 inserciones. En dicha página se creará de forma aleatoria la información que le pidamos, de acuerdo al tipo de dato de las columnas de nuestras tablas.
Una vez en la página de mockaroo veremos algo como
Primero generaremos la información para las tablas empleados y productos, pues recordemos que en la tabla ventas hay dos columnas con llaves foráneas que depende de la información de estas primeras dos tablas.
empleados.¶Comenzamos por realizar una consulta
SELECT * FROM empleados;
con base en lo anterior escribimos en mockaroo
es decir, comenzamos colocando el nombre de las columnas de la tabla. Posteriormente agregamos el tipo de dato que le corresponde
agregamos el tipo de dato para las columnas nombre, app, apm buscando en el menú que se despliega de pulsar en las flechas (lo que está en color rojo). En la columna nombre se generarán nombres aleatorios (first name) y en las columnas app, apm se generarán apellidos (last name). Después, trabajaremos con las columnas salario y horario. Para salario, en el menú que se despliega de pulsar la flechas buscaremos la opción number
lo que nos arroja
y personalizamos los valores para la columna salario. En nuestro caso colocamos
Después pasamos a la columna horario. Para ello, como le hicimos en la columna anterior, buscamos character sequence, con lo que obtendremos
y después lo personalizamos como
si tienes dudas de porqué colocamos lo anterior puedes pulsar el símbolo de ayuda y mockaroo arrojará una breve guía
Finalmente, en la columna puesto utilizaremos un poco de programación, pues en nuestro caso los puestos de trabajo son muy específicos. Supondremos que sólo hay cuatro puestos de trabajo en la papelería: cajero, vendedor, vigilante y limpieza. Para conseguir asignar a cada uno de los 1000 empleados que generaremos un puesto de trabajo haremos lo siguiente:
Ahí, la idea será la siguiente:
puesto (lo cual conseguimos al colocar en puesto _row number en mockaroo) les asignaremos el puesto de trabajo de cajero. El código que emplearemos será:if puesto <= 250 then 'cajero'es decir, si el número asignado al puesto es menor o igual a 250 entonces escribiremos en la columna puesto cajero.
elsif puesto <= 500 and puesto > 250 then 'vendedor'Y el resto queda como
elsif puesto <= 750 and puesto > 500 then 'vigilante'
else 'limpieza' endEn mockaroo tendríamos
pulsamos Apply para aplicar la fórmula del código que escribimos. Después previsualizamos los datos que se generaron. Para ello, en la pestaña principal pulsamos en Preview
donde:
una vez seleccionada la opción de SQL nos aparecerá una opción a la derecha denominada Table Name. En ella eliminamos el texto _MOCK_DATA_ y colocamos el nombre de nuestra tabla, la cual es empleados
Finalmente:
Ahora bien, realizaremos las 1000 inserciones en la tabla empleados, las cuales tenemos ya en el archivo que descargamos. No obstante, no iremos a pgAdmin para efectuar dichas inserciones pues muy seguramente esto sería muy lento debido al número de inserciones que creamos en el archivo .sql, además podríamos correr el riesgo de que nuestra pc se trabe. Para solucionar esto trabajaremos directamente con postgres. Cabe resaltar que pgAdmin es un cliente postgres, es decir, en él podemos utilizar posgres, siendo así que éste es un intermediario entre el usuario (nosotros) y PostgreSQL.
Para interactuar de manera directa con posgres utilizaremos la consola psql la cual descargamos en conjunto con pgAdmin. Para ello iremos al buscador de aplicaciones y escribimos justamente psql. Después de abrirlo nos debe arrojar la siguiente ventana
en la cual nos dice que estamos trabajando con el servidor local (nuestra pc). Damos enter y nos debe de aparecer
que hace alusión a la base de datos con la cual queremos trabajar. En mi caso, las tablas empleados, ventas, productos se encuentran en una base de datos denominada CursoPostgres, por lo que en
Database [postgres]:
colocaremos CursoPostgres. Después damos enter hasta que nos pide la contraseña que registramos en pgAdmin
una vez ingresada la contraseña damos enter y nos debería de aparecer algo como
con lo cual hemos logrado realizar la conexión directa con postgres. De hecho, desde dicha consola (y una vez realizada la conexión) podemos utilizar código sql. Por ejemplo, desde la consola podemos realizar una consulta
Ahora bien, cerramos la consola psql y ahora abrimos símbolos del sistema. Ahí, escribiremos
cd C:\Program Files\PostgreSQL\13\bin
En mi caso escribí esa dirección, pero puede que en tu pc dicho archivo tenga otra dirrección y que la versión sea superior a la 13. Para hallar la dirección debes ir a archivos>>este equipo>>Disco local>> archivos de programa y buscar la carpeta PostgreSQL. La abrimos hasta llegar a la carpeta bin , abrimos también esta carpeta y copiamos dicha dirección para después pegarla en la consola
Ahora, en la consola escribimos psql -U postgres -d CursoPostgres -f " " donde colocaremos entre las comillas la dirección donde guardamos el archivo .sql que descargamos de mockaroo:
psql -U postgres -d CursoPostgres -f "C:\Users\weeee\OneDrive\Escritorio\empleados.sql"
Note que escribimos -d CursoPostgres, es decir, después de -d escribimos el nombre de la base de datos que contiene la tabla empleados.
Una vez que damos enter se nos pedirá la contraseña del postgres. Así, cuando damos enter en la contraseña se comenzarán a ejecutar los inserts en la tabla empleados
Así, los inserts se han ejecutado. Después veamos los resultados en pgAdmin
-- Realizamos la consulta
SELECT * FROM empleados;
Primeras 8 filas:
Últimas filas
productos¶Comenzamos por ver la estructura de esta tabla
SELECT * FROM productos;
Después generamos los datos en mockaroo, en este caso no serán 1000 inserciones, nos bastarán con generar sólo 100 productos de diferentes marcas
es importante ser cauteloso a la hora de generar los datos. Después abrimos la consola psql y realizamos la conexión con postgres
no olvides que en Database [postgres]: debes colocar el nombre de la base de datos que almacena la tabla productos. Luego, vamos a símbolos del sistema para ejecutar las inserciones
una vez ingresada la contraseña tendremos
que las inserciones se han ejecutado. Comprobamos yendo a pgAdmin, recuerda que antes de realizar la consulta debe refrescar (refresh) la tabla o el esquema público
SELECT * FROM productos;
Primeras 8 filas
últimas filas
Notamos que la información, en la mayoría de los casos, no tiene sentido, pero el punto es tener datos para trabajar con ellos.
Finalizamos realizando las inserciones de la tabla ventas, en ella de igual forma sólo efectuaremos 100 inserciones.
ventas¶Comenzamos por
SELECT * FROM ventas;
Hay un detalle en esta tabla. En realidad, de inicio, no construimos bien la tabla ventas pues la columna cantidad está de más. La razón es que, de acuerdo a lo que aprenderemos después, podemos calcular el total de la venta a partir de la columna precio de la tabla productos y de la columna cantidad de la tabla ventas. De tal manera, no es necesario incluir dicha columna en esta tabla. Así, procedemos a eliminar dicha columna. Para ello escribimos
-- Eliminamos la columna total
ALTER TABLE ventas DROP COLUMN total;
-- Vemos los cambios
SELECT * FROM ventas;
Ahora, tenemos otro problema proveniente de la tabla empleados. Si has estado realizando los ejercicios que se dejan en clase, entonces seguramente eliminaste alguna fila de la tabla empleados. Aunque se haya eliminado una fila en dicha tabla, el índice generado en automático por el tipo de dato SERIAL continúa almacenado en dicha tabla, de tal manera, la fila siguiente a la fila eliminada tendrá como id el número sucesor de la fila eliminada. Por ejemplo, en mi caso
eliminé la fila 3, no obstante, el índice del id_empleados excluye de la numeración al número 3. Ahora bien, el problema radica en la columna id_empleado de la tabla ventas pues, si ingresamos en dicha columna el número 3, entonces postgres nos arrojará un error referencial debido a la llave foránea definida en dicha columna. Para resolver el problema
De donde
y en la fórmula escribimos
Una vez descargado el archivo vamos a símbolos del sistema para efectuar las inserciones
y vemos en pgAdmin la actualización de la tabla ventas
SELECT * FROM ventas;
y los últimos registros
Para realizar lo siguiente, puedes utilizar las libretas de Python en google colab.
Lo que haremos primero será instalar la librería Faker, la cual nos permite generar información falsa. Para ello
# Instalamos la libreria
!pip install Faker
Requirement already satisfied: Faker in c:\programdata\miniconda3\lib\site-packages (15.3.3) Requirement already satisfied: python-dateutil>=2.4 in c:\programdata\miniconda3\lib\site-packages (from Faker) (2.8.2) Requirement already satisfied: six>=1.5 in c:\programdata\miniconda3\lib\site-packages (from python-dateutil>=2.4->Faker) (1.16.0)
Cargamos las librerías a utilizar y damos un vistazo a algunas funciones de dicha librería
# Datos falsos
from faker import Faker
# Numeros y selecciones aleatorias
import random
# impresion mas bonita
import pprint
# Configuramos la libreria faker para generar datos en español (Mexico)
fake = Faker(['es_MX'])
# diccionario donde guardaremos toda la info
info = dict()
# Para generar una edad:
# generamos un numero aleatorio entero entre el 18 y 90
edad = random.randint(18, 90)
info['edad'] = edad
# Para el sexo, definimos una lista
sexo = ["M", "F", "O"]
# Con la libreria random podemos hacer una seleccion aleatoria de la lista
# sexo mediante la funcion choice()
sexo1 = random.choice(sexo)
info['sexo'] = sexo1
# Para el sueldo (0,1)
# Con la funcion random() generamos un numero aletorio decimal en el
# intervalo (0,1)
# 0.6879878789 * 10000 = 6879.878789---->6879.87
sueldo = round(random.random() * random.randint(1000, 100000),2)
info['sueldo'] = sueldo
# generamos un nombre aleatorio
nombre = fake.name()
info['nombre'] = nombre
# una direccion
direc = fake.address()
info['dirección'] = direc
# un trabajo
trabajo = fake.job()
info['trabajo'] = trabajo
# una ciudad
ciudad = fake.city()
info['ciudad'] = ciudad
# un curp
curp = fake.curp()
info['curp'] = curp
# Veamos hasta el momento toda la informacion que generamos
pprint.pprint(info)
{'ciudad': 'San Manuel de la Montaña',
'curp': 'BUYN570418MVZBCY09',
'dirección': 'Prolongación República de Moldova 158 Edif. 909 , Depto. 661\n'
'Nueva Países Bajos, HGO 28192-9641',
'edad': 28,
'nombre': 'Bianca Espartaco Badillo Prieto',
'sexo': 'F',
'sueldo': 40607.08,
'trabajo': 'Empleado de biblioteca'}
Ahora bien, supongamos que queremos obtener información para la tabla productos, de modo que queremos obtener información para las columnas id_productos, cantidad_almacen, precio, nombre_productos. Para ello podríamos generar
# simulacion de la info para 5 filas
productos = ["Lapiz", "Goma", "Cartulina", "Colores"]
for id in range(1,6):
# info
cantidad_almacen = random.randint(0,100)
precio = round(random.random() * random.randint(1,1000),2)
nombre_produtos = random.choice(productos)
# instruccion
cadena = f"INSERT INTO productos VALUES ({cantidad_almacen}, {precio}, '{nombre_produtos}');"
# mostramos
print(cadena)
INSERT INTO productos VALUES (41, 626.63, 'Colores'); INSERT INTO productos VALUES (16, 465.09, 'Goma'); INSERT INTO productos VALUES (88, 65.95, 'Cartulina'); INSERT INTO productos VALUES (58, 291.06, 'Colores'); INSERT INTO productos VALUES (11, 199.46, 'Lapiz');
Donde, con dichos inserts, ya podríamos ejecutarlos en pgAdmin. Podríamos copiar dichos inserts y después pegarlos en alguna Query tool en pgAdmin, no obstante, lo que haremos será crear un archivo que tenga todos los inserts y después lo ejecutaremos utilizando la consola, así como hicimos con los datos generados en Mockaroo. Para ello
# Creamos un archivo txt
inserts = open("DatosPython.txt","w")
# lista de productos
productos = ["Lapiz", "Goma", "Cartulina", "Colores", "Plumas", "Cuadernos"]
# Obtendremos 100 registros
for id in range(1,101):
# info
cantidad_almacen = random.randint(0,100)
precio = round(random.random() * random.randint(1,1000),2)
nombre_produtos = random.choice(productos)
# instruccion
cadena = f"INSERT INTO productos VALUES ({cantidad_almacen}, {precio}, '{nombre_produtos}');"
# escribiremos la cadena de texto anterior en el archivo txt
inserts.write(cadena)
# Salto de linea en el archivo txt
inserts.write("\n")
# Cerramos el archivo txt
inserts.close()
Dicho archivo se descargará en automático, después lo abrimos y lo guardaremos como un archivo .sql
Una vez que ya tenemos dicho archivo (y sabemos la ubicación en la que se encuentra), procedemos a realizar el mismo proceso en consola como lo vimos antes.
Adicionalmente, si queremos otro tipo de información para nuestras tablas, podemos utilizar, como se vio, la librería Faker, recordemos que
pprint.pprint(info)
{'ciudad': 'San Manuel de la Montaña',
'curp': 'BUYN570418MVZBCY09',
'dirección': 'Prolongación República de Moldova 158 Edif. 909 , Depto. 661\n'
'Nueva Países Bajos, HGO 28192-9641',
'edad': 28,
'nombre': 'Bianca Espartaco Badillo Prieto',
'sexo': 'F',
'sueldo': 40607.08,
'trabajo': 'Empleado de biblioteca'}
por lo que podríamos generar nombres, CURPS, direcciones falsas, entre otros más.
Aquí otro ejemplo (se requieren conocimientos en las librerías SQLite y Pandas) a la hora de generar información falsa Libreta en google colab
De las tablas adicionales que creaste (es decir aquellas tablas que no son empleados, productos, ventas) en la base de datos (referente al proyecto). Crea las inserciones utilizando cualquiera de los dos métodos que vimos en esta clase, es decir, generando los datos en Mockaroo o en Python.