1.5. Creación de tablas y restricciones
Una base de datos está formada por un conjunto de tablas que nos permitirán estructurar la información que percibimos en un escenario concreto del mundo real. Cada tabla almacenará en forma de registros la serie de ejemplos de cada clase de entidades o relaciones entre entidades especificadas previamente. Para crear una tabla de registros vacía con el comando CREATE TABLE es necesario primero declarar sus atributos.
CREATE TABLE nombre
(campo1 tipo1 [NOT NULL, AUTO_INCREMENT],
campo2 tipo2 [NOT NULL, AUTO_INCREMENT],
...
campoN tipoN [NOT NULL, AUTO_INCREMENT],
PRIMARY KEY (campox, campoy, ...),
[FOREIGN KEY (campox, campoy, ...)
REFERENCES tabla(campoa, campob, ...)]);
A la hora de definir la clase de información que almacenaremos en cada atributo, MySQL proporciona una gran variedad de tipos numéricos y alfanuméricos básicos (tabla 2). El espacio de memoria requerido para almacenar cada variable depende de la precisión especificada en cada caso. Los tipos DATE y TIME resultan especialmente útiles para llevar el registro de nuestras actividades en el tiempo. El usuario puede declarar, además, variables del tipo objeto (en inglés, Binary Large Objects o BLOB) para almacenar ficheros de texto, documentos en formato PDF o incluso imágenes dentro de alguna tabla de la base de datos.
Tabla 2. Tipos de datos en MySQL.
| Tipo genérico | Tipos MySQL |
| Entero | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT |
| Decimal | DECIMAL, FLOAT, DOUBLE/REAL |
| Texto | CHAR, VARCHAR, TINYTEXT, TEXT |
| Objetos | BLOB, MEDIUMBLOB, LONGBLOB |
| Tiempo | DATE, TIME |
Junto con la declaración de los atributos, para crear una tabla debemos especificar qué atributo o combinación de atributos será la clave primaria, identificando de forma unívoca cada instancia. En caso de existir, las claves foráneas para referenciar a los atributos de otras tablas también deben indicarse explícitamente.
El diseñador puede activar dos controles internos sobre el valor de un atributo en el momento de registrar nuevas instancias en la base de datos. En primer lugar, es posible rechazar aquellos registros que no posean un valor definido para un atributo concreto. Esta circunstancia se especifica con la construcción NOT NULL, justo después de la declaración de tipos. NOT NULL sería una restricción de campo obligatorio, no acepta valores nulos. En caso contrario, el sistema asignará por defecto el valor NULL a ese campo y aceptará valores nulos. Este requerimiento debe satisfacerse inexcusablemente en aquellos atributos que pertenecen a la clave primaria. En segundo lugar, para los identificadores numéricos asociados a cada instancia, el propio sistema puede encargarse de gestionar un contador automático de valores mediante la construcción AUTO_INCREMENT.
Volviendo nuevamente a nuestra base de datos catalogo, nos encontramos ahora en disposición de crear las tablas del catálogo de genes especificadas formalmente a continuación. Debemos escoger adecuadamente los tipos de datos para cada atributo o campo, según su contenido, definiendo claramente cuáles son las claves primarias y foráneas. El usuario puede empezar creando las tablas más elementales, es decir, aquellas que no poseen claves foráneas (genomas y funciones). Observad cómo declaramos la clave primaria y nos aseguramos de que ninguna instancia puede darse de alta en la base de datos con un valor nulo para las claves primarias, especie y función. Para verificar que el proceso de creación ha funcionado correctamente, el usuario puede consultar en la base de datos sobre las tablas existentes con el comando SHOW TABLES.
mysql> SHOW TABLES;
Empty set (0,00 sec)
mysql> CREATE TABLE genomas
-> especie VARCHAR(100) NOT NULL,
-> nombre VARCHAR(100),
-> descripcion TEXT,
-> PRIMARY KEY (especie));
Query OK, 0 row affected (0.28 sec)
mysql> CREATE TABLE funciones
-> funcion VARCHAR(20) NOT NULL,
-> descripcion VARCHAR(100),
-> PRIMARY KEY (funcion));
Query OK, 0 row affected (0.03 sec)
mysql> SHOW TABLES;
+--------------------+
| Tables_in_catalogo |
+--------------------+
| funciones |
| genomas |
+--------------------+
2 rows in set (0.00 sec)
Es posible revisar la definición de una tabla con la instrucción DESCRIBE:
mysql> DESCRIBE genomas; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | especie | varchar(100) | NO | PRI | NULL | | | nombre | varchar(100) | YES | | NULL | | | descripcion | text | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
A continuación, para crear la tabla genes, además de la clave primaria, indicamos un campo o atributo que es la clave foránea (que debe apuntar o hacer referencia a la clave primaria de la tabla genomas):
mysql> CREATE TABLE genes
-> (nombre VARCHAR(20) NOT NULL,
-> cromosoma VARCHAR(5),
-> hebra VARCHAR(1),
-> inicio INT,
-> final INT,
-> proteina VARCHAR(20),
-> especie VARCHAR(100),
-> PRIMARY KEY (nombre),
-> FOREIGN KEY (especie)
-> REFERENCES genomas(especie));
Query OK, 0 rows affected (0.06 sec)
mysql> DESCRIBE genes;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nombre | varchar(20) | NO | PRI | NULL | |
| cromosoma | varchar(5) | YES | | NULL | |
| hebra | varchar(1) | YES | | NULL | |
| inicio | int(11) | YES | | NULL | |
| final | int(11) | YES | | NULL | |
| proteina | varchar(20) | YES | | NULL | |
| especie | varchar(100)| YES | MUL | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
Finalmente, creamos la tabla anotaciones para relacionar los genes con las anotaciones funcionales. Junto con los dos valores que identifican cada registro (gen y funcion), añadiremos un atributo para registrar el origen de la información:
mysql> CREATE TABLE anotaciones
-> (nombre VARCHAR(20) NOT NULL,
-> funcion VARCHAR(20) NOT NULL,
-> origen VARCHAR(20),
-> PRIMARY KEY (nombre, funcion),
-> FOREIGN KEY (nombre)
-> REFERENCES genes(nombre),
-> FOREIGN KEY (funcion)
-> REFERENCES funciones(funcion));
Query OK, rows affected (0.11 sec)
mysql> DESCRIBE anotaciones;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| nombre | varchar(20) | NO | PRI | NULL | |
| funcion | varchar(20) | NO | PRI | NULL | |
| origen | varchar(20) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Es posible definir otras restricciones a los campos de las tablas con el comando CHECK.
Por ejemplo, podemos indicar que un campo numérico como el campo incio de la tabla genes que es tipo numérico solo acepte valores positivos CHECK (inicio >0), o que un campo de tipo cadena de caracteres solo acepte determinados valores; por ejemplo, para que el campo hebra de la tabla genes solo acepte los caracteres «+» o «-», podemos hacer hebra ENUM(‘+‘,’-‘).
Una vez creada la tabla podemos modificarla con la instrucción ALTER TABLE.
Por ejemplo, si queremos eliminar el campo origen de la tabla anotaciones escribimos
ALTER TABLE anotaciones DROP COLUMN origen;
y si queremos volver a añadir el mismo campo escribimos
ALTER TABLE anotaciones ADD origen VARCHAR(20);
Durante el tiempo de vida de una base de datos es frecuente que debamos actualizar su contenido. En determinados casos, esto puede implicar la eliminación completa de usuarios, de tablas o incluso, de la propia base de datos. Para implementar estos servicios, MySQL posee la familia de comandos DROP.
DROP DATABASE basededatos; DROP USER usuario; DROP TABLE tabla;