DDL (Data Definition Language)

DDL (Data definition language)

El Lenguaje de Definición de Datos (DDL) de SQL permite definir los objetos de nuestras bases de datos. Con él podremos crear bases de datos, tablas, claves, etc, así como crear vistas parciales de las tablas y gestionar los distintos permisos para cada usuario o grupo de usuarios, etc.

Quizás varía un poco de un SGBD a otro (tipo de datos, etc). Nosotros nos basaremos en MySQL.

Instrucciones sobre bases de datos

• Para mostrar las bases de datos que tenemos:

SHOW DATABASES;

• Para crear una nueva base de datos:

CREATE DATABASE `nombre_base_de_datos`;

• Para trabajar con una base de datos (que ya esté creada):

USE `nombre_base_de_datos`;

• Para consultar con qué base de datos estamos trabajando:

SELECT database();

• Para eliminar una base de datos que ya no queremos:

DROP DATABASE `nombre_base_de_datos`;

Antes de ver las instrucciones sobre las tablas, veremos primero los tipos de datos que acepta MySQL para crear las columnas de las tablas.

Tipo de datos

Algunos de los tipos de datos de MySQL son:

CLASIFICACIÓN	TIPO DE DATOS	DESCRIPCIÓN
ENTEROS	TINYINT	Enter entre -127 y 127
	BOOL	Al igual que TINYINT. True=1. False=0
	SMALLINT	Hasta 65.535
	MEDIUMINT	Hasta 8 millones (+-).
	INT o INTEGER	Hasta 4000 millones (+-)
	BIGINT	Hasta 18 trillones (+-)
DECIMALES	DECIMAL(m, d)
	FLOAT
	DOUBLE
	REAL
FECHA Y HORA	DATE	Guarda fechas en año-mes-día (0000-00-00)
	TIME	Guarda las horas en hora: minutos: según (00:00:00). Con la función DATE_FORMAT() se puede mostrar en AM y PM.
	DATETIME o TIMESTAMP	Fecha y hora. Formato: año-mes-día hora:minutos:según (0000-00-00 00:00:00).
	YEAR	4 dígitos
TEXTO	CHAR(n)	Hasta 255 caracteres antes de la versión 5.03 Hasta 65.000 en versiones posteriores.
	VARCHAR(n)	Ocupa menos que CHAR pero las consultas son más lentas.
OTROS	BLOB	Información binaria (imágenes, sonidos...)

Instrucciones sobre tablas

• Para mostrar información de las tablas existentes:

  Instrucciones

  SHOW TABLES;            --  Muestra las tablas de la BD en uso
  

  SHOW TABLES FROM nombre_base_de_datos;  --  Muestra las tablas de una BD
  

  DESCRIBE nombre_tabla; -- Muestra los campos de una tabla (o DESC o EXPLAIN)
  

  SHOW CREATE TABLE nombre_tabla; -- Muestra el CREATE TABLE de la tabla
  

  SHOW INDEX FROM nombre_tabla; -- Muestra los índices de una tabla
  

• Para crear/modificar/eliminar las tablas:

  Instrucciones

  CREATE DATABASE nombre_base_de_datos;   // Crear una base de datos
  

  CREATE TEMPORARY TABLE nombre_tabla...  // Crear una tabla temporal
  

  ALTER TABLE nombre_tabla... // Modificar estructura de una tabla
  

  DROP TABLE nombre_base_de_datos;    // Eliminar una base de datos
  

Ahora veremos en detalle estas últimas.

Creación de tablas

Creación de tablas

Para crear una tabla, utilizamos la sentencia CREATE TABLE. A continuación veremos ejemplos de cómo crear tablas. Los nombres (de tablas, campos, etc) deben empezar por letra y no pueden contener espacios o caracteres de puntuación especiales.

CREATE TABLE alumnos (
codigo INT,
nombre VARCHAR (20)
);

Claves primarias y alternativas

Claves primarias y alternativas

Cuando creamos una tabla, es importante indicar qué campo o campos son clave primaria (PK) y cuáles son las claves alternativas (UK). La clave primaria es un campo que identifica de forma única cada registro de la tabla. Las claves alternativas son campos que también pueden identificar de forma única un registro, pero no son la clave principal. Cuando creamos una tabla, si queremos indicar que un campo es clave primaria utilizaremos PRIMARY KEY. Para indicar que es clave alternativa, usaremos: UNIQUE.

CREATE TABLE personas (
codigo INT PRIMARY KEY AUTO_INCREMENT,
dni VARCHAR(10) UNIQUE,
nombre VARCHAR (40) NOT NULL,
deuda   INT DEFAULT 0
);   

La clave primaria y/o las claves alternativas también se pueden indicar después de la última columna:

Claves primarias y alternativas al final

CREATE TABLE personas (
codigo INT AUTO_INCREMENT,
dni VARCHAR(10),
nombre VARCHAR (40) NOTE NULL,
PRIMARY KEY(codigo), 
UNIQUE (dni)
);

En ese caso, también podemos poner nombres a las restricciones (que deben ser únicos en toda la base de datos). Una utilidad es poder borrar después una restricción indicando su nombre.

Poner nombre a las restricciones

CREATE TABLE personas (
    codigo INT AUTO_INCREMENT,
    dni VARCHAR(10),     
    nombre VARCHAR (40) NOTE NULL,
    CONSTRAINT cpri_personas PRIMARY KEY(codigo),
    CONSTRAINT calt_personas UNIQUE(dni)
);

Luego podríamos borrar una restricción con:

DROP INDEX calt_personas WHERE personas;
ALTER TABLE personas DROP INDEX calt_personas;

Claves compuestas

Si queremos que la clave primaria sea compuesta (es decir, que esté formada por varios campos), lo indicamos así (hay que poner la restricción al final):

CREATE TABLE lineasFactura (
    num INT,
    linea   INT,
    ...,
    PRIMARY KEY(num, linea)
);

Clausulas de las tablas

• AUTO_INCREMENT: si no indicamos el código cuando insertamos una persona, automáticamente le asignará uno más del máximo código de los alumnos insertados.

  AUTO_INCREMENT

  CREATE TABLE personas (
      codigo INT AUTO_INCREMENT,
      dni VARCHAR(10) UNIQUE,
      nombre VARCHAR (40) NOT NULL,
      deuda INT DEFAULT 0,
      PRIMARY KEY(codigo)
  );
  

• NOT NULL: para impedir que un campo tenga valores nulos.

  NOT NULL

  CREATE TABLE personas (
      codigo INT AUTO_INCREMENT,
      dni VARCHAR(10) UNIQUE,
      nombre VARCHAR (40) NOT NULL,
      deuda INT DEFAULT 0,
      PRIMARY KEY(codigo)
  );
  

• DEFAULT : para poner un valor por defecto.

  DEFAULT

  CREATE TABLE personas (
      codigo INT AUTO_INCREMENT,
      dni VARCHAR(10) UNIQUE,
      nombre VARCHAR (40) NOT NULL,
      deuda INT DEFAULT 0, -- Si no se indica, será 0
      PRIMARY KEY(codigo)
  );
  

Creación de tablas con claves ajenas

Las claves ajenas (FK (Foreign Key)) son campos que hacen referencia a la clave primaria de otra tabla. Se utilizan para establecer relaciones entre tablas.

   CREATE TABLE pueblos (
      cpo INT PRIMARY KEY,
      nombre CHAR(30)
   ) ENGINE = InnoDB;

   CREATE TABLE clientes (
      codigo INT PRIMARY KEY,
      nombre VARCHAR (40) NOT NULL,
      pueblo INT,
      tel VARCHAR(15),
      FOREIGN KEY (pueblo) REFERENCES pueblos(cpo)
   ) ENGINE = InnoDB;

En este ejemplo vemos cómo sería la definición de una clave ajena compuesta:

   CREATE TABLE cintas (
      cod_pel INT,
      num_copia INT,
      rebobinada BOOL,
      PRIMARY KEY(cod_pel, num_copia)
   ) ENGINE = InnoDB;

   CREATE TABLE prestamos (
      pel INT,
      copia INT,
      fecha DATE,
      socio INT,
      PRIMARY KEY(pel, copia, fecha),
      FOREIGN KEY(pel, copia) REFERENCES cintas(cod_pel, num_copia)
   ) ENGINE = InnoDB;

Constraints

• También se puede poner un nombre en la clave ajena, con el CONSTRAINT.
• Es opcional indicar la columna de la tabla referenciada (cpo) a menos que en la tabla padre fuera clave alternativa (en lugar de clave principal).

FOREIGN KEY(pueblo) REFERENCIAS pueblos (cpo)

Insertar, modificar y elimninar información

Las sentencias de INSERT, UPDATE y DELETE se utilizan para insertar, modificar o eliminar información de las tablas. Estas sentencias forman el DML y se verán más adelante en detalle, pero aquí veremos un pequeño resumen.

• INSERT INTO: Inserta una nueva fila en una tabla.
• UPDATE: Modifica una o más filas de una tabla.
• DELETE FROM: Elimina una o más filas de una tabla.

Ahora vamos a ver un ejempplo de creación de una base de datos y sus tablas, utilizaremos el ejemplo anterior de clientes y pueblos.

Ejemplo de creación de una base de datos y sus tablas

Primero creamos la base de datos:

CREATE DATABASE emp;

Luego seleccionamos la base de datos para trabajar con ella:

use emp;

Ahora creamos las tablas:

CREATE TABLE pueblos (
    cpo INT PRIMARY KEY,
    nombre CHAR(30)
);

CREATE TABLE clientes (
    codigo INT PRIMARY KEY,
    nombre VARCHAR (40) NOT NULL,
    pueblo INT,
    tel VARCHAR(15),
    FOREIGN KEY (pueblo) REFERENCES pueblos(cpo)
);

Ahora que tenemos la estructura de la base de datos, podemos insertar datos en las tablas. Aunque esto lo veremos en el tema siguiente DML como hemos avisado antes, ahora veremos un ejemplo (sin entrar en detalle)

Insertar información en las tablas

Para insertar información en las tablas, utilizamos la sentencia INSERT INTO. Por ejemplo, para insertar un pueblo y un cliente:

INSERT INTO pueblos VALUES (46410, 'Sueca');
INSERT INTO clientes VALUES (1, 'Pep', 46410);

La tabla de clientes tiene una clave ajena (pueblo) que hace referencia a la tabla de pueblos. Por lo tanto, al insertar un cliente, debemos asegurarnos de que el pueblo ya existe en la tabla de pueblos. Vemos tres ejemplos, el primero es correcto, el segundo da error porque la clave primaria ya existe y el tercero da error porque la clave ajena no existe en la tabla de pueblos:

INSERT INTO clientes VALUES (1, 'Pep', 46410); // No da problemas
INSERT INTO clientes VALUES (1, 'Pepa', 46410); // Error por clave primaria
INSERT INTO clientes VALUES (2, 'Pepa', 77777); // Error por clave ajena

Ahora veamos el funcionamiento de las claves ajenas al eliminar registros.

Eliminar información de las tablas

Cuando eliminamos un registro de una tabla que tiene una clave ajena, debemos tener en cuenta que si hay registros en la tabla hija que hacen referencia a ese registro, no podremos eliminarlo. Por ejemplo, si intentamos eliminar un pueblo que tiene clientes asociados, nos dará error:

DELETE FROM pueblos WHERE cpo = 46410; // Error por la clave ajena

En cambio si borramos un pueblo que no tiene clientes asociados, no habrá problema:

DELETE FROM pueblos WHERE cpo = 55555; // No da problemas

Acciones asociadas a la clave ajena

• ON DELETE CASCADE Cuando definimos una clave ajena podemos especificar que, si se borra un registro de la tabla padre (la de pueblos), que se borran también, automáticamente, los registros relacionados de la tabla hija (es decir: que se eliminan también clientes de ese pueblo).
• ON UPDATE CASCADE Podemos indicar que, cuando modificamos un código postal en la tabla de pueblos, que también se modifique ese código en los correspondientes clientes que tenían ese pueblo.

Con estas acciones podemos intervenir en el comportamiento de las claves ajenas.

Por ejemplo: Imaginemos una tabla facturas con sus tabla relacionada lineas. Ahora imaginemos en una aplicación de gestión que para poder borrar una factura, primero debemos borrar todas las líneas de esa factura. Esto no tiene sentido (Al usuario le sería demasiado engorroso tener que borrar primero las líneas de la factura y luego la factura). Para este caso tenemos ON DELETE CASCADE, que borra automáticamente las líneas de la factura cuando borramos la factura.

Ahora imaginemos que tenemos una tabla provincias y una tabla comarcas, donde cada comarca pertenece a una provincia. Si queremos modificar el código de una provincia, queremos que se modifique automáticamente en las comarcas que pertenecen a esa provincia. Para este caso tenemos ON UPDATE CASCADE, que actualiza automáticamente el código de la provincia en las comarcas.

Si llevamos estos ejemplos a nuestra base de datos de empresa emp. Tenemos la clave ajena de clientes hacia pueblos. Podemos conseguir que:

• Si se eliminar un pueblo, se eliminen automáticamente todos los clientes de ese pueblo.
• Si se modifica el código de un pueblo, se modifique automáticamente en todos los clientes de ese pueblo.

Entonces la creación de la tabla clientes sería así:

Tabla cientes con claves ajenas

CREATE TABLE clientes (
    codigo INT PRIMARY KEY,
    nombre VARCHAR (40) NOT NULL,
    pueblo INT,
    tel VARCHAR(15),
    FOREIGN KEY (pueblo) REFERENCES pueblos(cpo)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;

Acciones ON DELETE y ON UPDATE

Recordar que si queremos volver a crear una tabla (que ya se creo previamente), primero debemos eliminarla. Si no, nos dará error.

DROP TABLE clientes;

Existe una manera de modificar una tabla ya creada sin tener que borrarla. Para ello utilizamos la sentencia ALTER TABLE, que veremos más adelante.

Pero a parte de CASCADE, también podemos poner otras acciones:

• CASCADE: Los registros dependientes también se borran o modifican.

• SET NULL: Los registros dependientes se ponen a null (si no tenían la restricción de NOT NULL, claro).

• SET DEFAULT: Los registros dependientes se ponen en el valor por defecto (si lo tenían, claro). El motor InnoDB lo acepta, pero sin efecto alguno.

• RESTRICT: No deja borrar/modificar de la tabla padre si tiene registros dependientes. Es la opción por defecto (si no ponemos ON UPDATE o ON DELETE).

• NO ACTION: MySQL hace exactamente lo mismo que RESTRICT. Ahora bien, en otros SGBD como Oracle o PostgreSQL, NO ACTION deja borrar/modificar de la tabla padre pero no hace nada en los registros dependientes. Esto rompería la definición de clave ajena, pero lo que hace realmente es retrasar las comprobaciones de clave ajena para cuando termine la transacción (ya veremos las transacciones más adelante).

Ejercicios con soluciones

Ejercicio 1

Modifica el esquema de la base de datos de empresa, la tabla de clientes.

• Primero prueba con CASCADE tanto para ON DELETE como para ON UPDATE. Inserta y modifica datos en las tabla de prueblos y obserba ¿Qué ocurre?
• Segundo, prueba con SET NULL. Ahora vuele a insertar y modificar datos en las tabla de prueblos y observa ¿Qué ocurre?

Ejercicio 2

1. Para realizar un estudio sobre los tipos de cultivos que se dan en cada comarca del mundo, se ha creado el siguiente esquema ER, con el posterior esquema relacional:

Escribe las instrucciones DDL necesarias para crear la base de datos “cultivos” con las tablas correspondientes, teniendo en cuenta que:

• Antes de crear una tabla con claves ajenas, habrá que crear primero las tablas de las que depende. Si no, te dará error cuando intentes crear esa tabla.
• El código de los tipos de cultivos será un char(3). El código de comarcas un char(5). La cantidad de habitantes (n_hab) será un entero. Los demás campos serán un varchar(30).
• Habrá que indicar las claves ajenas de forma que:
  • Si modificamos el código de un país o provincia en la tabla de provincias, también deberá cambiar en la tabla de comarcas.
  • Si modificamos un código de cultivo en la tabla tipo_cultivos, también se deberá modificar en la tabla cultivar.
  • si borramos un tipo de cultivo en la tabla tipo_cultivos, también se tendrán que borrar los registros de cultivar que tengan ese cultivo.

Ejercicios con soluciones

• Ejercicios con soluciones

ALTER TABLE

Se utiliza para modificar la estructura (no los datos) de una tabla ya existente.

1. Cambiar el nombre de una tabla:

   ALTER TABLE nombre_antiguo RENAME TO nombre_nuevo;
   

2. Añadir un campo

   ALTER TABLE tabla ADD COLUMN campo INT NOT NULL;
   ALTER TABLE tabla ADD COLUMN campo VARCHAR (20) NOT NULL FIRST;
   ALTER TABLE tabla ADD COLUMN campo INT NOT NULL AFTER campo_existent
   

   Nota: COLUMN es opcional en ADD, DROP, CHANGE y ALTER

3. Eliminar un campo

   ALTER TABLE tabla DROP COLUMN campo_a_borrar;
   

4. Modificar un campo

   • Cambio de nombre (y/o del tipo de datos, de posición...):

   ALTER TABLE tabla CHANGE COLUMN campo_actual
        campo_nuevo INT NOT NULL 
        AFTER campo_exi;
   

   • Poner o quitar el valor por defecto:

   ALTER TABLE tabla ALTER COLUMNO campo
     [SET DEFAULT valor | DROP DEFAULT];
   

5. Añadir claves o índices

   ALTER TABLE tabla ADD PRIMARY KEY (campos); // 1 o más campos separados por comas   
   ALTER TABLE tabla ADD FOREIGN KEY (campos) REFERENCES tabla_padre (campos);
   ALTER TABLE tabla ADD UNIQUE [nombre_index ] (campos); // Clave alternativa
   ALTER TABLE tabla ADD INDEX [nombre_index] (campos); // Para búsquedas más rápidas
   

   Nota: también podemos poner el CONSTRAINT para poner nombre a las restricciones.

6. Borrar claves o índices

   ALTER TABLE tabla DROP PRIMARY KEY;      // Borra clave primaria
   ALTER TABLE tabla DROP FOREIGN KEY nombre_clave\_ali; // Borra clave ajena
   ALTER TABLE tabla DROP INDEX nombre_index;       // Borra clave alternativa o índice
   

DROP TABLE

Esta sentencia se utiliza para eliminar una tabla de la base de datos. Si la tabla no existe, no da error, pero si queremos que no dé error si la tabla no existe, podemos poner IF EXISTS.

DROP TABLE

Los sistemas de gestión de bases de datos no piden confirmación al eliminar una tabla, por lo que debemos tener cuidado al utilizar esta sentencia, ya que se perderán todos los datos de la tabla. No hay posibilidad de deshacer la operación.

DROP TABLE [IF EXISTS] nombre_tabla;            // Elimina una tabla

EJERCICIOS. ALTER TABLE (BD empresa)

Ejercicio 1. (BD empresa)

Para practicar el alter table primero crearemos una nueva BD llamada empresa con un par de tablas. Crea la base de datos de nombre empresa con las siguientes tablas:

• Tabla de empleados con las columnas:

  • num entero,
  • nombre 10 caracteres, campo obligatorio
  • jefe_dep entero
  • comision entero
  • dept entero, campo obligatorio

• Tabla de departamentos con las columnas:

  • codigo entero, clave primaria
  • nombre 15 caracteres, campo oblibagorio, único
  • pueblo 20 caracteres

Ejercicio 2. (BD empresa)

Una vez creada la base de datos y las tablas, realiza las siguientes modificaciones:

1. Haz que el campo num de la tabla empleados sea la clave principal.
2. Añade una nueva columna llamada salario a la tabla empleados, de tipo entero y con tal de no poder tomar valores nulos.
3. Incorpora la condición de que la columna dept de la tabla empleados es clave ajena respecto a la tabla departamentos (campo código). Haz que cuando se borre un departamento, también se borran los empleados asociados a ese departamento.
4. Haz que la columna jefe_dep de la tabla empleados sea clave ajena, referenciando a su propia tabla.
5. Trate de eliminar la columna jefe_dept de la tabla empleados.
6. Elimina la condición de que la columna código de departamentos es clave primaria.

Ejercicio 3. (BD Empresa)

Añade una nueva restricción llamada chk_salario:

El campo salario debe tener un valor entre 0 y 9999.

Soluciones a los ejercicios 1 y 2

CREATE DATABASE empresa;
USE empresa;

CREATE TABLE empleados (
    num INT,
    nombre VARCHAR(10) NOT NULL,
    jefe_dep INT,
    comision INT,
    dept INT NOT NULL
);

CREATE TABLE departamentos (
    codigo INT PRIMARY KEY,
    nombre VARCHAR(15) NOT NULL UNIQUE,
    pueblo VARCHAR(20)
);
-- 1
ALTER TABLE empleados ADD PRIMARY KEY (num);
-- 2
ALTER TABLE empleados ADD salario INT NOT NULL;
-- 3
ALTER TABLE empleados ADD FOREIGN KEY (dept) REFERENCES departamentos(codigo) ON DELETE CASCADE;
-- 4
ALTER TABLE empleados ADD FOREIGN KEY (jefe_dep) REFERENCES empleados(num);
-- 5
ALTER TABLE empleados DROP COLUMN jefe_dep; -- error
-- 6
ALTER TABLE departamentos DROP PRIMARY KEY; -- error

solución ejericio 3

ALTER TABLE empleados
    ADD CONSTRAINT chk_salario
    CHECK (salario >= 0 AND salario <= 9999);