Creación de tablas

La sentencia CREATE TABLE

CREATE TABLE crea una tabla: construye el recipiente vacío, con sus campos, sus tipos y sus reglas. No guarda ningún dato. Después de ejecutarla, la tabla existe y está lista para recibir registros; antes de ejecutarla, no hay dónde poner nada.

La distinción entre el recipiente y el contenido ordena todo lo que sigue. La tabla es el formulario en blanco; los registros son los formularios llenos. Y son dos momentos distintos, con instrucciones distintas: la estructura se define una vez con CREATE TABLE, y los datos se cargan muchas veces con INSERT INTO. Esta instrucción pertenece a la familia DDL —definición— y no a DML, que es la de los datos.

Su forma es siempre la misma: el nombre de la tabla y, entre paréntesis, la lista de campos separados por comas. Cada campo lleva su nombre, su tipo, y —si hace falta— sus restricciones.

CREATE TABLE socios (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    nombre    VARCHAR(80)  NOT NULL,
    documento VARCHAR(15)  NOT NULL UNIQUE,
    correo    VARCHAR(120),
    telefono  VARCHAR(20),
    localidad VARCHAR(60),
    alta      DATE         NOT NULL,
    activo    TINYINT(1)   NOT NULL DEFAULT 1
);

Las restricciones

El tipo dice qué clase de dato entra en un campo. Las restricciones dicen qué se acepta y qué no dentro de esa clase. Son las reglas que la base va a hacer cumplir sola, y son la última línea de defensa de la calidad del dato. El programa pide cuatro:

Fijate en la diferencia de lugar: PRIMARY KEY, NOT NULL y UNIQUE se escriben en el renglón del campo, porque hablan de ese campo. FOREIGN KEY se escribe al final, como una línea aparte, porque habla de la relación entre dos tablas: necesita nombrar el campo local y la tabla y el campo ajenos. Esa es la razón de que se escriba distinto.

Entre NOT NULL y UNIQUE hay una confusión que conviene deshacer: no son lo mismo. NOT NULL obliga a llenar el campo; UNIQUE impide repetir el valor. Un correo puede ser obligatorio y repetible —dos hermanos pueden compartir uno— o único y opcional. Y hay un caso que sorprende a todos: en MySQL y en SQLite, un campo UNIQUE admite varios valores vacíos, porque un vacío no se considera igual a otro vacío. Si se quería que el dato fuera obligatorio y irrepetible —el caso del documento— hay que declarar las dos, como en el ejemplo.

Un último detalle: una clave primaria puede estar formada por más de un campo. En una tabla de inscripciones, la clave podría ser la pareja «alumno + materia»: por separado ninguno de los dos es único —un alumno se inscribe en varias materias y una materia tiene muchos alumnos— pero la combinación sí lo es. Se declara al final, igual que la clave foránea: PRIMARY KEY (alumno_id, materia_id).

Un ejemplo completo

Estas son las tres tablas de la biblioteca que se usan en toda la unidad: los socios, los libros y los préstamos. Se declaran en orden, porque cada una necesita que las anteriores existan:

CREATE TABLE socios (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    nombre    VARCHAR(80)  NOT NULL,
    documento VARCHAR(15)  NOT NULL UNIQUE,
    correo    VARCHAR(120),
    telefono  VARCHAR(20),
    localidad VARCHAR(60),
    alta      DATE         NOT NULL,
    activo    TINYINT(1)   NOT NULL DEFAULT 1
);

CREATE TABLE libros (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(120) NOT NULL,
    autor  VARCHAR(80)  NOT NULL
);

CREATE TABLE prestamos (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    socio_id INT NOT NULL,
    libro_id INT NOT NULL,
    salida   DATE NOT NULL,
    vuelta   DATE,
    FOREIGN KEY (socio_id) REFERENCES socios(id),
    FOREIGN KEY (libro_id) REFERENCES libros(id)
);

Leídas como reglas del sistema, esas tres declaraciones dicen bastante:

• Un socio tiene documento obligatorio y único: no puede haber dos personas con el mismo número.
• correo, telefono y localidad pueden quedar vacíos. Son la excepción, y están declarados como excepción.
• activo arranca en 1: los socios se crean activos salvo que se diga lo contrario.
• Un préstamo siempre tiene un socio y un libro, y los dos tienen que existir: son dos claves foráneas.
• vuelta no tiene NOT NULL, porque un préstamo sin devolver tiene la fecha vacía. Esa fecha vacía es justamente el dato que permite saber qué está prestado.

Fijate en algo que no parece un detalle: las claves foráneas guardan el identificador, no el nombre. Es la diferencia entre una base ordenada y una que acumula errores, y es lo que se resolvió con la normalización en la Unidad 1. Si prestamos guardara el nombre del socio y el título del libro, el día que alguien corrija una ortografía habría que actualizar todos sus préstamos, y cualquier préstamo cuyo nombre hubiera quedado mal escrito apuntaría a algo que ya no coincide con nada.

Relaciones entre tablas

Las tablas se relacionan entre sí, y hay tres formas posibles. Cada una se implementa de una manera distinta:

El caso uno a muchos es el más frecuente, y el muchos a muchos es el que más cuesta entender al principio. Como ninguna de las dos tablas puede guardar la referencia de la otra —un socio tendría que tener una lista de libros, y un libro una lista de socios— se crea una tercera tabla que representa el hecho que une a las dos. En la biblioteca, ese hecho es el préstamo.

Vale notar que prestamos no es «una tabla más»: es la que convierte dos relaciones uno a muchos en una relación muchos a muchos. Y tiene un dato propio —la fecha de salida y la de vuelta— que no pertenece ni al socio ni al libro, sino al hecho de que ese socio se llevó ese libro. Ese es el criterio para decidir qué campos van en la tabla intermedia: los que describen la relación, no los que describen a los extremos.

Cómo se crean las relaciones

Una relación no es un dibujo: es una restricción que queda declarada en la base y que el gestor hace cumplir. Y se puede crear de dos maneras —desde la interfaz de phpMyAdmin o escribiendo SQL—: las dos terminan produciendo exactamente la misma restricción. Conviene conocer las dos, porque la primera es más rápida para explorar y la segunda es la que queda escrita y la única que existe cuando no hay interfaz de por medio.

Desde el Diseñador

phpMyAdmin trae un Diseñador, en la pestaña con ese nombre. Muestra las tablas de la base como recuadros, con sus columnas, y las relaciones como líneas que unen el campo que guarda la referencia con la clave a la que apunta. Es la vista que mejor muestra el modelo de un vistazo: acá se ve que prestamos cuelga de socios y de libros, y que productos y ventas están sueltas porque todavía no se relacionaron con nada.

Para crear una relación desde acá se usa la herramienta Crear relación de la barra de la izquierda. Al elegirla se abre un diálogo que construye la clave foránea: se indican las dos columnas —la que guarda la referencia y la clave a la que apunta— y las dos acciones que la base va a tomar sola. El dialogo trae la sección FOREIGN KEY con los desplegables on delete y on update, que son las mismas dos preguntas de la vista de relaciones: qué hacer con este registro cuando se borre el de la otra tabla y qué hacer cuando se modifique su clave.

Las opciones son las mismas en los dos desplegables, y conviene saber qué hace cada una:

El Diseñador necesita el almacenamiento de configuración de phpMyAdmin: una base aparte con unas tablas internas —las pma__…— donde guarda las coordenadas de los recuadros y los datos de las relaciones. En la instalación del Anexo ya viene configurado; si no lo estuviera, la propia pantalla avisa y ofrece crearlo.

Desde la vista de relaciones

El otro camino gráfico es el formulario que aparece como pestaña Vista de relaciones dentro de la estructura de una tabla. Es menos visual y más preciso: muestra en una grilla todas las claves foráneas de esa tabla, y la última fila vacía sirve para agregar una nueva.

Cada fila pide lo mismo que el diálogo del Diseñador: qué columna, a qué base, a qué tabla y a qué columna apunta, más las dos acciones. Y el botón Previsualizar SQL, abajo de todo, no es un adorno: muestra la sentencia que se va a ejecutar antes de ejecutarla. Es la mejor prueba de que las dos formas son la misma cosa.

Con SQL

Lo que hacen esas dos pantallas, escrito en SQL, es un ALTER TABLE. Como las tablas ya existen, la restricción se agrega a una tabla que ya está en uso:

ALTER TABLE prestamos
    ADD CONSTRAINT fk_prestamos_libro
    FOREIGN KEY (libro_id) REFERENCES libros(id);

Se lee en el mismo orden en que se piensa: a la tabla prestamos, agregale una restricción llamada fk_prestamos_libro, que el campo libro_id sea clave foránea de libros(id). El nombre es opcional: si se omite, el gestor lo inventa como los ibfk del ejemplo anterior. Ponerlo a mano tiene una ventaja concreta, porque el día que haya que quitar la relación hay que nombrarla, y es más fácil acordarse de fk_prestamos_libro que de prestamos_ibfk_2.

Para quitar una relación también hay que nombrarla. No alcanza con decir «borrá la relación de libro_id»: hay que decir cuál, y eso se hace por su nombre:

ALTER TABLE prestamos
    DROP FOREIGN KEY fk_prestamos_libro;

Y como una relación es un dato que la base conoce, se puede consultar. Esta consulta lista todas las claves foráneas de la base, con su columna y su destino:

SELECT CONSTRAINT_NAME AS restriccion,
       COLUMN_NAME     AS columna,
       REFERENCED_TABLE_NAME  AS apunta_a,
       REFERENCED_COLUMN_NAME AS columna_ajena
FROM   information_schema.KEY_COLUMN_USAGE
WHERE  TABLE_SCHEMA = 'biblioteca'
  AND  REFERENCED_TABLE_NAME IS NOT NULL;

Comparar esa grilla con el formulario de la vista de relaciones deja la conclusión a la vista: la interfaz no guarda nada aparte. El Diseñador, la vista de relaciones y el ALTER TABLE escriben lo mismo —una restricción de clave foránea— y cualquiera de las tres sirve para comprobar que la relación quedó creada.

Integridad referencial

La integridad referencial es la garantía de que toda clave foránea apunta a un registro que existe. Dicho de otro modo: que no haya préstamos de socios inexistentes, ni facturas de clientes que no están. Es una propiedad que el gestor sostiene solo, sin que haya que programarla, y actúa en los dos sentidos:

• Al cargar. Si se intenta insertar un préstamo con un socio_id que no corresponde a ningún socio, la base lo rechaza. No hay forma de crear una referencia al vacío.
• Al eliminar. Si se intenta borrar un socio que tiene préstamos cargados, la base también lo rechaza. Es una protección: borrarlo dejaría esos préstamos apuntando a algo que ya no existe.

Ese segundo caso es el que más sorprende, porque aparece justo cuando se quiere hacer algo aparentemente razonable: dar de baja un socio. Y ahí la base obliga a tomar una decisión explícita, que se declara en el CREATE TABLE con la cláusula ON DELETE:

Archivos para practicar

Todo lo que se explica en esta página se puede probar de verdad, sin copiar y pegar sentencia por sentencia. Cada archivo crea su propia base, con las tablas y los datos ya cargados, y trae al final —comentadas— las consultas para practicar.

Cómo se importa

Importar es abrir el archivo .sql dentro del gestor para que él ejecute todas las sentencias de una sola vez. Son cuatro pasos, y son siempre los mismos:

1. Descargar el archivo desde la tabla de arriba y fijarse dónde quedó: casi siempre, en la carpeta Descargas.
2. Abrir phpMyAdmin —en XAMPP, el botón Admin que está en la fila de MySQL— y entrar a la pestaña Importar, la que está arriba de todo.
3. Elegir el archivo con el botón Seleccionar archivo. No hace falta crear antes la base de datos: el archivo la crea.
4. Apretar Importar y esperar el mensaje verde: «La importación se ha finalizado correctamente».

Si el mensaje es verde, ya está: la base aparece en la lista de la izquierda, con sus tablas y con los datos cargados. Si sale en rojo, phpMyAdmin dice en qué línea del archivo se trabó, y las dos causas más comunes son que la base ya existía o que el archivo se cortó al descargarse.

Cuidado con el nombre de la base. Al importar, cada archivo borra la base con ese nombre si ya existía y la vuelve a crear vacía. Es lo que permite repetir la práctica todas las veces que haga falta —siempre arranca igual—, pero también significa que no conviene guardar datos propios en una base que se llame biblioteca_1_tabla, biblioteca_2_tablas o biblioteca_3_tablas.

Resumen

• CREATE TABLE crea la estructura vacía: no guarda datos. Pertenece a DDL.
• Cada campo lleva nombre, tipo y restricciones, separados por comas.
• PRIMARY KEY identifica cada registro; UNIQUE impide repetir; NOT NULL obliga a llenar.
• NOT NULL y UNIQUE no son lo mismo, y un campo puede necesitar las dos.
• Un campo UNIQUE admite varios valores vacíos.
• La PRIMARY KEY puede estar formada por más de un campo.
• FOREIGN KEY se declara al final, porque relaciona dos tablas.
• Las claves foráneas guardan el identificador, nunca el nombre.
• Las relaciones son uno a muchos, muchos a muchos (con tabla intermedia) y uno a uno.
• En la tabla intermedia van los campos que describen la relación, no los extremos.
• La integridad referencial impide cargar referencias al vacío y borrar registros de los que dependen otros.
• ON DELETE RESTRICT es lo más seguro; CASCADE puede borrar en cadena y hay que evitarlo.

Ver la carga de datos