OBJETIVO:
Conocer la aplicación de las restricciones (constraint) de las estructuras de
almacenamiento (tablas), las cuales permiten proporcionar la integridad de la información.
DESARROLLO
En los ejercicios que se mostrarán a continuación, se deberá tomar de base el modelo de datos de la
base que se ha estado utilizando.
Procederemos a crear las tablas, y se conocerán algunos de detalles a considerar durante la
construcción.
UNIVERSIDAD FRANCISCO GAVIDIA FACULTAD DE INGENIERIA Y ARQUITECTURA
PRACTICA CICLO I - 2015
Asignatura: Diseño de Base de Datos Grupo: V01
Profesor: Ing. Luis Enrique Reyes Fecha desarrollo:
sábado 25/04/2015 Aula: Virtual
PRACTICA: CREACION DE TABLAS Y CONSTRAINT con MySQL
Veremos la justificación del uso de las restricciones dentro de la creación de las estructuras:
Unicidad
Dominios
Nulidad
Dependencias
Indices
EJECUCIÓN DE LA PRÁCTICA.
Ejercicio 1.
Creación de tabla definiendo el nombre de la restricción:
CREATE TABLE regions (
region_id INT NOT NULL,
region_name VARCHAR(25)
);
En el caso del campo region_name, al no colocar la frase NOT NULL, significa que el campo
no es obligatorio que posea información (le pudimos haber puesto la frase NULL).
Ejercicio 2.
Creación de una llave primaria luego de haber creado una tabla:
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ;
En este caso, después de haber creado la tabla regions, podemos indicarle al DBMS que
requerimos agregar una llave primaria a la tabla, también se le puede dar un nombre a la
restricción de llave primaria cuyo nombre sería reg_id_pk, para el campo region_id.
Ejercicio 3.
Otra forma de hacer directamente el ejercicio 1 y 2:
CREATE TABLE regions (
region_id INTEGER NOT NULL PRIMARY KEY,
region_name VARCHAR(25)
);
Si en este caso se le muestra el mensaje de que la tabla ya existe, entonces deberá utilizar la
siguiente instrucción antes de crearla:
DROP TABLE regions;
Luego intente crear la tabla de este ejercicio.
Ejercicio 4.
Creación de las tablas definiendo al final la llave primaria de la tabla:
CREATE TABLE countries (
country_id CHAR(2) ,
country_name VARCHAR(40) ,
region_id INT ,
CONSTRAINT country_c_id_pk PRIMARY KEY (country_id)
);
A diferencia del ejercicio anterior, otra forma de crear una llave primaria es colocar al final de
la instrucción el CONSTRAINT para crear la llave primaria.
Ejercicio 5.
Creación de una dependencia después de haber creado una tabla:
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
En este caso, definimos una restricción la cual indica que se creará una llave primaria, este es el
caso cuando ya existe una tabla y deseamos que dependan sus datos de otra tabla.
Vemos que cada restricción (CONSTRAINT) le asignamos un nombre. Esto es importante
cuando queremos borrarlo en algún momento.
En este caso la tabla countries, dependerá de la tabla regions. El enlace será el campo region_id
en ambas tablas.
Ejercicio 6.
El ejercicio 4 y 5 definido en una sola instrucción.
CREATE TABLE countries (
country_id CHAR(2) NOT NULL ,
country_name VARCHAR(40) ,
region_id INT ,
CONSTRAINT country_c_id_pk PRIMARY KEY (country_id) ,
CONSTRAINT countr_reg_fk FOREIGN KEY (region_id)
REFERENCES regions(region_id)
);
Ejercicio 7.
Creación de la tabla Location, siguiendo el modelo de datos y otra forma de crear la llave primaria
y la llave foránea.
CREATE TABLE locations (
location_id INT(4) NOT NULL AUTO_INCREMENT,
street_address VARCHAR(40),
postal_code VARCHAR(12),
city VARCHAR(30) NOT NULL,
state_province VARCHAR(25),
country_id CHAR(2) ,
CONSTRAINT loc_id_pk PRIMARY KEY (location_id),
CONSTRAINT loc_c_id_fk FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;
Ejercicio 8.
Creación de la tabla departments
CREATE TABLE departments (
department_id INTEGER(4) NOT NULL PRIMARY KEY
AUTO_INCREMENT,
department_name VARCHAR(30) NOT NULL,
manager_id NUMERIC(6),
location_id INTEGER(4),
CONSTRAINT dept_loc_fk FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;
Ejercicio 9.
Creación de la tabla job, para crear otras dependencias según el modelo
CREATE TABLE jobs (
job_id VARCHAR(10),
job_title VARCHAR(35) NOT NULL,
min_salary NUMERIC(6),
max_salary NUMERIC(6),
CONSTRAINT job_id_pk PRIMARY KEY(job_id)
) ;
Ejercicio 10.
Al crear la tablas y al incluir condiciones de validación entre campos.
CREATE TABLE job_history (
employee_id INTEGER(6) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
department_id INTEGER(4),
CONSTRAINT jhist_date_interval CHECK (end_date > start_date)
) ;
Vemos que la restricción indica que al ingresar las fechas de inicio y fin, la condición será que
la fecha fin siempre deberá ser mayor, nunca menor o igual.
Ejercicio 11.
Creación de las tablas que incluyen validación de datos, llave única y que corresponde a la última
tabla a crear debido a las dependencias:
CREATE TABLE employees (
employee_id INTEGER(6),
first_name VARCHAR(20),
last_name VARCHAR(25) NOT NULL,
email VARCHAR(25) NOT NULL,
phone_number VARCHAR(20),
hire_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
salary NUMERIC(8,2),
commission_pct NUMERIC(4,2),
manager_id INTEGER(6),
department_id INTEGER(4),
CONSTRAINT emp_salary_min CHECK (salary > 0),
CONSTRAINT emp_email_uk UNIQUE (email)
);
En este caso al campo emp_salary_min, se le está indicando que el valor del salario deberá
tener como valor mínimo 100, nunca serán valores negativos.
En el caso del campo emp_email_uk, lo que se le indica es que el contenido del campo nunca
deberá repetirse. Normalmente estas son llamadas llaves alternas.
Ejercicio 12.
Podremos definir varias dependencias hacia otras tablas.
ALTER TABLE employees
ADD (
CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)
) ;
ALTER TABLE employees
ADD (
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_job_fk FOREIGN KEY (job_id)
REFERENCES jobs (job_id),
CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;
En este caso, se hacen dos ALTER TABLE debido a que hay que crear primero la llave primaria
de la tabla empleado pues en seguida será utilizada para crear una llave foránea.
Ejercicio 13.
Creación de las tablas con llaves primarias compuestas:
ALTER TABLE job_history
ADD (
CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date),
CONSTRAINT jhist_job_fk FOREIGN KEY (job_id)
REFERENCES jobs (job_id),
CONSTRAINT jhist_emp_fk FOREIGN KEY (employee_id)
REFERENCES employees (employee_id),
CONSTRAINT jhist_dept_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
) ;
En este ejercicio se muestra que la llave primaria es una llave compuesta. Para ello,
simplemente colocamos los campos separados por comas para indicar cuáles son los esos
campos. Debe tenerse cuidado en el orden que se colocan pues normalmente cuando se crea
una llave primaria, automáticamente se crea un índice y no será lo mismo que se cambie el
orden de los campos por efecto de rendimiento y búsqueda.
Ejercicio 14.
Creación de índices simples
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
En este caso, el índice creado es en la tabla regions, en el que se le indica el campo region_id.
La palabra UNIQUE, está indicando que no habran datos duplicados.
Una aclaración importante es que los índices se crean automáticamente en orden ascendente.
Ejercicio 15.
Creación de índices simples controlando el orden en que estarán los datos
CREATE UNIQUE INDEX jhist_st_date_pk
ON job_history (start_date) ;
En este caso, el índice creado es en la tabla job_history, en el que se le indica el campo
start_date.
Una aclaración en este caso es que el índice se creará de manera descendente, es decir, a la hora
de hacer búsqueda de datos por medio de este campo el registro más inmediato es el que posea
una fecha más reciente.
Ejercicio 16.
Creación de índices compuestos en el que incluye dos campos. Lo mismo puede suceder si se
tienen índices de más de dos campos.
CREATE INDEX emp_name_ix
ON employees (last_name, first_name);
En este caso la table employees tendrá un índice compuesto por dos campos.
El uso de este tipo de índices debe ser bien analizado, en el sentido de cuál debería ser la
búsqueda de datos más frecuentes. Esto significa que si la búsqueda más frecuente es primero
por nombre y luego por apellido deberá cambiarse la forma de creación del índice de la
siguiente manera:
CREATE INDEX emp_name_ix
ON employees (first_name, last_name);