Definición de creación  

A continuación de las definiciones de las columnas podemos añadir otras definiciones. La sintaxis más general es:

    definición_columnas
  | [CONSTRAINT [símbolo]] PRIMARY KEY (index_nombre_col,...)
  | KEY [nombre_index] (nombre_col_index,...)
  | INDEX [nombre_index] (nombre_col_index,...)
  | [CONSTRAINT [símbolo]] UNIQUE [INDEX] 
       [nombre_index] [tipo_index] (nombre_col_index,...)
  | [FULLTEXT|SPATIAL] [INDEX] [nombre_index] (nombre_col_index,...)
  | [CONSTRAINT [símbolo]] FOREIGN KEY 
       [nombre_index] (nombre_col_index,...) [definición_referencia]
  | CHECK (expr)

Veremos ahora cada una de estas opciones.

Índices

Tenemos tres tipos de índices. El primero corresponde a las claves primarias, que como vimos, también se pueden crear en la parte de definición de columnas.

Claves primarias

La sintaxis para definir claves primarias es:

    definición_columnas
  | PRIMARY KEY (index_nombre_col,...)

El ejemplo anterior que vimos para crear claves primarias, usando esta sintaxis, quedaría así:

mysql> CREATE TABLE ciudad4 (nombre CHAR(20) NOT NULL,
    -> poblacion INT NULL DEFAULT 5000,
    -> PRIMARY KEY (nombre));
Query OK, 0 rows affected (0.17 sec)

Pero esta forma tiene más opciones, por ejemplo, entre los paréntesis podemos especificar varios nombres de columnas, para construir claves primarias compuestas por varias columnas:

mysql> CREATE TABLE mitabla1 (
    -> id1 CHAR(2) NOT NULL,
    -> id2 CHAR(2) NOT NULL,
    -> texto CHAR(30),
    -> PRIMARY KEY (id1, id2));
Query OK, 0 rows affected (0.09 sec)

mysql>

Índices

El segundo tipo de índice permite definir índices sobre una columna, sobre varias, o sobre partes de columnas. Para definir estos índices se usan indistintamente las opciones KEY o INDEX.

mysql> CREATE TABLE mitabla2 (
    -> id INT,
    -> nombre CHAR(19),
    -> INDEX (nombre));
Query OK, 0 rows affected (0.09 sec)

O su equivalente:

mysql> CREATE TABLE mitabla3 (
    -> id INT,
    -> nombre CHAR(19),
    -> KEY (nombre));
Query OK, 0 rows affected (0.09 sec)

También podemos crear un índice sobre parte de una columna:

mysql> CREATE TABLE mitabla4 (
    -> id INT,
    -> nombre CHAR(19),
    -> INDEX (nombre(4)));
Query OK, 0 rows affected (0.09 sec)

Este ejemplo usará sólo los cuatro primeros caracteres de la columna 'nombre' para crear el índice.

Claves únicas

El tercero permite definir índices con claves únicas, también sobre una columna, sobre varias o sobre partes de columnas. Para definir índices con claves únicas se usa la opción UNIQUE.

La diferencia entre un índice único y uno normal es que en los únicos no se permite la inserción de filas con claves repetidas. La excepción es el valor NULL, que sí se puede repetir.

mysql> CREATE TABLE mitabla5 (
    -> id INT,
    -> nombre CHAR(19),
    -> UNIQUE (nombre));
Query OK, 0 rows affected (0.09 sec)

Una clave primaria equivale a un índice de clave única, en la que el valor de la clave no puede tomar valores NULL. Tanto los índices normales como los de claves únicas sí pueden tomar valores NULL.

Por lo tanto, las definiciones siguientes son equivalentes:

mysql> CREATE TABLE mitabla6 (
    -> id INT,
    -> nombre CHAR(19) NOT NULL,
    -> UNIQUE (nombre));
Query OK, 0 rows affected (0.09 sec)

Y:

mysql> CREATE TABLE mitabla7 (
    -> id INT,
    -> nombre CHAR(19),
    -> PRIMARY KEY (nombre));
Query OK, 0 rows affected (0.09 sec)

Los índices sirven para optimizar las consultas y las búsquedas de datos. Mediante su uso es mucho más rápido localizar filas con determinados valores de columnas, o seguir un determinado orden. La alternativa es hacer búsquedas secuenciales, que en tablas grandes requieren mucho tiempo.

Claves foráneas

En MySQL sólo existe soporte para claves foráneas en tablas de tipo InnoDB. Sin embargo, esto no impide usarlas en otros tipos de tablas.

La diferencia consiste en que en esas tablas no se verifica si una clave foránea existe realmente en la tabla referenciada, y que no se eliminan filas de una tabla con una definición de clave foránea. Para hacer esto hay que usar tablas InnoDB.

Hay dos modos de definir claves foráneas en bases de datos MySQL.

El primero, sólo sirve para documentar, y, al menos en las pruebas que he hecho, no define realmente claves foráneas. Esta forma consiste en definir una referencia al mismo tiempo que se define una columna:

mysql> CREATE TABLE personas (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> nombre VARCHAR(40),
    -> fecha DATE);
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE telefonos (
    -> numero CHAR(12),
    -> id INT NOT NULL REFERENCES personas (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE); (1)
Query OK, 0 rows affected (0.13 sec)

mysql>

Hemos usado una definición de referencia para la columna 'id' de la tabla 'telefonos', indicando que es una clave foránea correspondiente a la columna 'id' de la tabla 'personas' (1). Sin embargo, aunque la sintaxis se comprueba, esta definición no implica ningún comportamiento por parte de MySQL.

La otra forma es mucho más útil, aunque sólo se aplica a tablas InnoDB.

En esta forma no se añade la referencia en la definición de la columna, sino después de la definición de todas las columnas. Tenemos la siguiente sintaxis resumida:

CREATE TABLE nombre
       definición_de_columnas
       [CONSTRAINT [símbolo]] FOREIGN KEY [nombre_index] (nombre_col_index,...) 
       [REFERENCES nombre_tabla [(nombre_col,...)]
        [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
        [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
        [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]]

El ejemplo anterior, usando tablas InnoDB y esta definición de claves foráneas quedará así:

mysql> CREATE TABLE personas2 (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> nombre VARCHAR(40),
    -> fecha DATE)
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE telefonos2 (
    -> numero CHAR(12),
    -> id INT NOT NULL,
    -> KEY (id), (2)
    -> FOREIGN KEY (id) REFERENCES personas2 (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE) (3)
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql>

Es imprescindible que la columna que contiene una definición de clave foránea esté indexada (2). Pero esto no debe preocuparnos demasiado, ya que si no lo hacemos de forma explícita, MySQL lo hará por nosotros de forma implícita.

Esta forma define una clave foránea en la columna 'id', que hace referencia a la columna 'id' de la tabla 'personas' (3). La definición incluye las tareas a realizar en el caso de que se elimine una fila en la tabla 'personas'.

ON DELETE <opción>, indica que acciones se deben realizar en la tabla actual si se borra una fila en la tabla referenciada.

ON UPDATE <opción>, es análogo pero para modificaciones de claves.

Existen cinco opciones diferentes. Veamos lo que hace cada una de ellas:

• RESTRICT: esta opción impide eliminar o modificar filas en la tabla referenciada si existen filas con el mismo valor de clave foránea.
• CASCADE: borrar o modificar una clave en una fila en la tabla referenciada con un valor determinado de clave, implica borrar las filas con el mismo valor de clave foránea o modificar los valores de esas claves foráneas.
• SET NULL: borrar o modificar una clave en una fila en la tabla referenciada con un valor determinado de clave, implica asignar el valor NULL a las claves foráneas con el mismo valor.
• NO ACTION: las claves foráneas no se modifican, ni se eliminan filas en la tabla que las contiene.
• SET DEFAULT: borrar o modificar una clave en una fila en la tabla referenciada con un valor determinado implica asignar el valor por defecto a las claves foráneas con el mismo valor.

Por ejemplo, veamos esta definición de la tabla 'telefonos':

mysql> CREATE TABLE telefonos3 (
    -> numero CHAR(12),
    -> id INT NOT NULL,
    -> KEY (id),
    -> FOREIGN KEY (id) REFERENCES personas (id)
    -> ON DELETE RESTRICT ON UPDATE CASCADE) 
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql>

Si se intenta borrar una fila de 'personas' con un determinado valor de 'id', se producirá un error si existen filas en la tabla 'telefonos3' con mismo valor en la columna 'id'. La fila de 'personas' no se eliminará, a no ser que previamente eliminemos las filas con el mismo valor de clave foránea en 'teléfonos3'.

Si se modifica el valor de la columna 'id' en la tabla 'personas', se modificarán los valores de la columna 'id' para mantener la relación.

Veamos un ejemplo más práctico:

personas
id    nombre        fecha
1     Fulanito      1998/04/14
2     Menganito     1975/06/18
3     Tulanito      1984/07/05

telefonos3
numero     id
12322132   1
12332221   1
55546545   3
55565445   3

Si intentamos borrar la fila correspondiente a "Fulanito" se producirá un error, ya que existen dos filas en 'telefonos' con el valor 1 en la columna 'id'.

Sí será posible borrar la fila correspondiente a "Menganito", ya que no existe ninguna fila en la tabla 'telefonos' con el valor 2 en la columna 'id'.

Si modificamos el valor de 'id' en la fila correspondiente a "Tulanito", por el valor 4, por ejemplo, se asignará el valor 4 a la columna 'id' de las filas 3ª y 4ª de la tabla 'telefonos3':

personas
id    nombre        fecha
1     Fulanito      1998/04/14
2     Menganito     1975/06/18
4     Tulanito      1984/07/05

telefonos3
numero     id
12322132   1
12332221   1
55546545   4
55565445   4

No hemos usado todas las opciones. Las opciones de MATCH FULL, MATCH PARTIAL o MATCH SIMPLE no las comentaremos de momento (lo dejaremos para más adelante).

La parte opcional CONSTRAINT [símbolo] sirve para asignar un nombre a la clave foránea, de modo que pueda usarse como identificador si se quiere modificar o eliminar una definición de clave foránea. También veremos esto con más detalle en capítulos avanzados.

Otras opciones

Las opiones como FULLTEXT o SPATIAL las veremos en otras secciones.

La opción CHECK no está implementada en MySQL.