Integridad referencial en MySQL

Estrictamente hablando, para que un campo sea una clave fornea, ste necesita ser definido como tal al momento de crear una tabla. Se pueden definir claves forneas en cualquier tipo de tabla de MySQL, pero nicamente tienen sentido cuando se usan tablas del tipo InnoDB.

A partir de la versin 3.23.43b, se pueden definir restricciones de claves forneas con el uso de tablas InnoDB. InnoDB es el primer tipo de tabla que permite definir estas restricciones para garantizar la integridad de los datos.

Para trabajar con claves forneas, necesitamos hacer lo siguiente:

  • Crear ambas tablas del tipo InnoDB.
  • Usar la sintaxis FOREIGN KEY(campo_fk) REFERENCES nombre_tabla (nombre_campo)
  • Crear un ndice en el campo que ha sido declarado clave fornea.

InnoDB no crea de manera automtica ndices en las claves forneas o en las claves referenciadas, as que debemos crearlos de manera explcita. Los ndices son necesarios para que la verificacin de las claves forneas sea ms rpida. A continuacin se muestra como definir las dos tablas de ejemplo con una clave fornea.

CREATE TABLE cliente
(
    id_cliente INT NOT NULL,
    nombre VARCHAR(30),
    PRIMARY KEY (id_cliente)
) TYPE = INNODB;

CREATE TABLE venta
(
    id_factura INT NOT NULL,
    id_cliente INT NOT NULL,
    cantidad   INT,
    PRIMARY KEY(id_factura),
    INDEX (id_cliente),
    FOREIGN KEY (id_cliente) REFERENCES cliente(id_cliente)
) TYPE = INNODB;

La sintaxis completa de una restriccin de clave fornea es la siguiente:

[CONSTRAINT smbolo] FOREIGN KEY (nombre_columna, ...)
                  REFERENCES nombre_tabla (nombre_columna, ...)
                  [ON DELETE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
                  [ON UPDATE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]

Las columnas correspondientes en la clave fornea y en la clave referenciada deben tener tipos de datos similares para que puedan ser comparadas sin la necesidad de hacer una conversin de tipos. El tamao y el signo de los tipos enteros debe ser el mismo. En las columnas de tipo caracter, el tamao no tiene que ser el mismo necesariamente.

Si MySQL da un error cuyo nmero es el 1005 al momento de ejecutar una sentencia CREATE TABLE, y el mensaje de error se refiere al nmero 150, la creacin de la tabla fall porque la restriccin de la clave fornea no se hizo de la manera adecuada. De la misma manera, si falla una sentencia ALTER TABLE y se hace referencia al error nmero 150, esto significa que la definicin de la restriccin de la clave fornea no se hizo adecuadamente. A partir de la versin 4.0.13 de MySQL, se puede usar la sentencia SHOW INNODB STATUS para ver una explicacin detallada del ltimo error que se gener en relacin a la definicin de una clave fornea.

Si en una tabla, un registro contiene una clave fornea con un valor NULO, significa que no existe niguna relacin con otra tabla.

A partir de la versin 3.23.50, se pueden agregar restricciones de clave fornea a una tabla con el uso de la sentencia ALTER TABLE. La sintaxis es:

ALTER TABLE nombre_tabla ADD [CONSTRAINT smbolo] FOREIGN KEY(...)
REFERENCES otra_tabla(...) [acciones_ON_DELETE][acciones_ON_UPDATE]

Por ejemplo, la creacin de la clave fornea en la tabla venta que se mostr anteriormente pudo haberse hecho de la siguiente manera con el uso de una sentencia ALTER TABLE:

CREATE TABLE venta
(
    id_factura INT NOT NULL,
    id_cliente INT NOT NULL,
    cantidad   INT,
    PRIMARY KEY(id_factura),
    INDEX (id_cliente)
) TYPE = INNODB;

ALTER TABLE venta ADD FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente);

En las versiones 3.23.50 y menores no deben usarse las sentencias ALTER TABLE o CREATE INDEX en tablas que ya tienen definidas restricciones de claves forneas o bien, que son referenciadas en restricciones de claves forneas: cualquier sentencia ALTER TABLE elimina todas las restricciones de claves forneas definidas para la tabla.

No debe usarse una sentencia ALTER TABLE en una tabla que est siendo referenciada, si se quiere modificar el esquema de la tabla, se recomienda eliminar la tabla y volverla a crear con el nuevo esquema. Cuando MySQL hace un ALTER TABLE, puede que use de manera interna un RENAME TABLE, y por lo tanto, se confundan las restricciones de clave fornea que se refieren a la tabla. Esta restriccin aplica tambin en el caso de la sentencia CREATE INDEX, ya que MySQL la procesa como un ALTER TABLE.

Cuando se ejecute un script para cargar registros en una base de datos, es recomendable agregar las restricciones de claves forneas va un ALTER TABLE. De esta manera no se tiene el problema de cargar los registros en las tablas de acuerdo a un orden lgico (las tablas referenciadas deberan ir primero).

COMPARTE ESTE ARTÍCULO

ENVIAR A UN AMIGO
COMPARTIR EN FACEBOOK
COMPARTIR EN TWITTER
COMPARTIR EN GOOGLE +
ARTÍCULO ANTERIOR

¡SÉ EL PRIMERO EN COMENTAR!
Conéctate o Regístrate para dejar tu comentario.