Ver índice
Tablas InnoDB (MySQLi)

        Ocultar índice  

   Índice de contenidos
   Instalación en Windows
   Instalación en Ubuntu
   Servidores seguros
   Páginas dinámicas
   Sintaxis básica
   Operaciones
   Arrays
   Formatos de presentación
   Operadores
   Bucles
   Extraer y ord. información
   Funciones
   Ficheros externos
   Imágenes dinámicas
   Gestión de directorios
   Cookies y sesiones
   Clases y objetos
   Ficheros en formato PDF
   Bases de datos MySQL
   PHP y XML
   PDO - Bases SQLite / MySQL
   MySQL a traves de misqli
   Algo de JavaScript y AJAX


Las tablas InnoDB

Creación de una tabla InnoDB

La creación de tablas de este tipo no presenta ninguna dificultad añadida. El proceso es idéntico a las tablas habituales sin más que añadir Type=InnoDB después de cerrar el paréntesis de la sentencia de creación de la tabla.

Este script crea una tabla InnoDB con idénticos campos a los utilizados en el caso de la tabla MyISAM que hemos visto en páginas anteriores. La sintaxis, muy similar a la utilizada allí es esta:

<?php
#definimos (en minusculas) el nombre de la nueva tabla
$tabla="demoinno";
# escribimos la cadena que contiene la sentencia de creación de la nueva tabla
$crear="CREATE TABLE  $tabla (";
$crear.="Contador TINYINT(8)  UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,";
$crear.="DNI CHAR(8) NOT NULL,  ";
$crear.="Nombre VARCHAR (20)  NOT NULL, ";
$crear.="Apellido1 VARCHAR (15)  not null, ";
$crear.="Apellido2 VARCHAR (15)  not null, ";
$crear.="Nacimiento DATE DEFAULT '1970-12-21', ";
$crear.="Hora TIME DEFAULT '00:00:00', ";
$crear.="Sexo Enum('M','F') DEFAULT 'M' not null, ";
$crear.="Fumador CHAR(0) , ";
$crear.="Idiomas SET(' Castellano',' Francés','Inglés',
         ' Alemán',' Búlgaro',' Chino'), ";
$crear.=" PRIMARY KEY(DNI), ";
$crear.=" UNIQUE auto (Contador)";
$crear.=")"; 
# esta es la única diferencia con el proceso de
# creación de tablas MyISAM
 $crear.=" Type=InnoDB";
/* tenemos completa la sentencia MYSQL  solo falta ejecutarla crear la conexión y ejecutarla */

/*incluimos los parámetros de conexión */
include('mysqli.inc.php');
/* incluimos dos opciones de proceso y de programacion orientada a objetos. Podemos ejecutar
los ejemplos pasado mediante el metodo GET el tipo elegido.  */

    if(!empty ($_GET['tipo'])){
        $tipo_script=$_GET['tipo'];
    }else{
        $tipo_script="objetos";
    }


/*En caso de tipo proceso utilizaríamos esto */

if ($tipo_script=="proceso"){
    /******    Programación mediante procesos ***********/
    #conexion, selección de base de datos y verificacion de errores segun ejemplos anteriores
    $conexion=@mysqli_connect ($cfg_servidor,$cfg_usuario,$cfg_password,$cfg_basephp1);
    if(!mysqli_connect_errno()==0){
        print "<Mbr>No ha podido realizarse la conexión mediante procesos<br>";
        print "Error número: ". mysqli_connect_errno()." equivalente a: ". mysqli_connect_error();
        exit();
    }
    # Creación. Los parámetros requieren el orden aquí indicado al revés del caso mysql

    if(mysqli_query($conexion,$crear)){
            print "La tabla ha sido CREADA";
    }else{
            print "<br>No ha podido crearse la base de datos mediante procesos<br>";
            print "Error : ". mysqli_error($conexion);
            exit();
    }
    mysqli_close($conexion);

}

/* en caso de programacion orientada a objetos */
if ($tipo_script=="objetos"){
      #conexion, selección de base de datos y verificacion de errores segun ejemplos anteriores
    $objeto=@new mysqli ($cfg_servidor,$cfg_usuario,$cfg_password,$cfg_basephp1);
    if(!$objeto->connect_errno==0){
        print "
No ha podido realizarse la conexión mediante objetos<br>"; print "Error número: ". $objeto->connect_errno." equivalente a: ". $objeto->connect_error; exit(); }
if($objeto->query($crear)){ print "La tabla ha sido CREADA"; }else{ print "<br>No ha podido crearse la tabla mediante objetos<br>"; print "Error : ". $objeto->error; exit(); } $objeto->close(); } ?>
  ¡Cuidado!  

Bajo Windows, al crear una base de datos o tabla InnoDB el nombre de la misma aparecerá en minúsculas independientemente de la sintaxis que hayamos utilizado en su creación.
Para prever posibles conflictos al ejecutar los scripts en diferentes plataformas utilizaremos minúsculas para los nombres de tablas.

Una vez creadas, las tablas InnoDB se comportan –a efectos de uso– exactamente igual que las que hemos venido utilizando en las páginas anteriores. No es preciso hacer ningún tipo de modificación en la sintaxis. Por tanto, es totalmente válido todo lo ya comentado respecto a: altas, modificaciones, consultas y bajas.

Las transacciones

Uno de los riesgos que se plantean en la gestión de bases de datos es que pueda producirse una interrupción del proceso mientras se está actualizando una o varias tablas. Pongamos como ejemplo el cobro de nuestra nómina. Son necesarias dos anotaciones simultáneas: El cargo en la cuenta del organismo pagador y el abono en nuestra cuenta bancaria. Si se interrumpiera fortuitamente el proceso en el intermedio de las dos operaciones podría darse la circunstancia de que apareciera registrado el pago sin que se llegaran a anotar los haberes en nuestra cuenta.

Las transacciones evitan este tipo de situaciones ya que los datos se registran de manera provisional y no se consolidan hasta que una instrucción confirme que esas anotaciones tienen carácter definitivo. Hemos de tener muy presente que para utilizar este tipo de opciones es imprescindible que las tablas sean del tipo InnoDB.

Sintaxis de las transacciones

Hay dos palabras que son claves para este tipo acciones. Commit, cuya traducción puede ser ejecutar y rollback que significa algo como no ejecutar. Cuando se trata de la extensión mysqli hay que tomar en consideración una tercera palabra, también clave, que es autocommit cuyo significado podría ser algo parecido a ejecutar de forma autómatica y que es configuración por defecto de la extensión mysqli.

Una buena opción es conocer el estado actual de la opción autocommit. Si su valor es TRUE significa que cualquier llamada a mysql_query, $obejeto->query, mysqli_stmt_execute o $actuacion-> execute() se ejecutaría de forma inmediata e irreversible. Por el contrario, si su valor fuera FALSE las instrucciones anteriores se ejecutarían de forma provisional pero no serían confirmadas hasta que se incluyera una orden commit o fueran descartadas por medio de una orden rollback.

Para conocer el estado actual de autocommit hemos de ejecutar la sentencia MySQL siguiente:

SELECT @@autocommit

qué siguiendo el tratamiento clásico de las consultas nos devolverá el valor TRUE (1) o FALSE (0) de su estado actual. Su valor por defecto es TRUE y las eventuales modificaciones que podamos hacer en nuestros script solo persisten hasta la finalización de la ejecución del mismo. La modificación de su estado requiere de una de estas funciones:

mysqli_autocommit($conexion,BOOLEANO )
o
$objeto->autocommit(BOOLEANO )

Siendo $conexion el identificador de conexión (programación mediante procesos) y $objeto el identificador del objeto mysqli (programación orientada a objetos. El valor BOOLEANO puede ser TRUE o FALSE. Como es lógico para poder utilizar las transacciones es necesario que ese valor sea FALSE.

Una vez configurado autocommit como false las sucesivas sentencias, sean simples, múltiples como preparadas, no se registrarán en la tabla hasta que no se incluya una orden explícita de hacerlo. Esa orden requiere el uso de una de estás dos funciones:

mysqli_commit($conexion )
o
$objeto->commit()

sentencias que advierte a MySQL (en modo procesos y objetos respectivamente) que ha finalizado la transacción y que SÍ debe hacer efectivos todos los cambios incluidos en ella. Si se optara por NO hacer efectivos los cambios habría que utilizar:

mysqli_rollback($conexion )
o
$objeto->rollback()

Precauciones a tener en cuenta

Cuando se utilizan campos autoincrementales en tablas InnoDB los contadores se van incrementando al añadir registros (incluso de forma provisional) con lo cual si se aborta la inclusión con un ROLLBACK ese contador mantiene el incremento y en inserciones posteriores partirá de ese valor acumulado.

Por ejemplo. Si partimos de una tabla vacía y hacemos una transacción de dos registros (número 1 y número 2 en el campo autoincremental) y la finalizamos con ROLLBACK, no se insertarán pero en una inserción posterior el contador autoincremental comenzará a partir del valor 2.

  ¡Cuidado!  

Cuando una instrucción modifica la condición de autocommit ese valor no recupera su valor por defecto (true) hasta que no se incluya una modificación explícita de su valor o hasta que finalice la ejecución del script actual.

Aquí tienes unos ejemplos de uso de transacciones

Ejecutar
Modo procesos
Ver código fuente
Modo procesos
Ejecutar
Orientado a objetos
Ver código fuente
Orientado a objetos

La integridad referencial

Elementos necesarios para la integridad referencial

La integridad referencial ha de establecerse siempre entre dos tablas. Una de ellas ha de comportarse como tabla principal (suele llamarse tabla padre y la otra sería la tabla vinculada ó tabla hijo. Es imprescindible:

Si observas el código fuente del ejemplo que tienes aquí debajo podrás observar que utilizamos el número del DNI (único para alumno) como elemento de vinculación de la tabla de datos personales con la que incluye las direcciones.

Integridad referencial en tablas InnoDB

Cuando se trabaja con varias tablas que tienen algún tipo de vínculo resulta interesante disponer de mecanismos que protejan o impidan acciones no deseadas. Supongamos, como veremos en los ejemplos posteriores que pretendemos utilizar una tabla con datos de alumnos y otra tabla distinta para las calificaciones de esos alumnos. Si no tomamos ninguna precaución (bien sea mediante los script o mediante el diseño de las tablas) podría darse la circunstancia de que incluyéramos calificaciones a alumnos inexistentes, en materias de las que no están matriculados, etcétera. También podría darse la circunstancia de que diéramos de baja a un alumno pero que se mantuvieran las calificaciones vinculadas a él. Todas estas circunstancias suelen producir efectos indeseados y las tablas InnoDB pueden ser diseñadas para prever este tipo de situaciones.

Sintaxis para la vinculación de tablas

Los vínculos entre tablas suelen establecer en el momento de la creación de la tabla vinculada.

CREATE TABLE tabla (campo1, campo2,...
        KEY
nombre (campo de vinculacion ),
        FOREIGN KEY (
campo de vinculacion)
        REFERENCES
nombre_de la tabla principal (Indice primario de la tabla principal)
) Type=InnoDB

donde el campo de vinculacion ha de ser un índice (no es necesario que sea PRIMARY KEY ni UNIQUE) y donde Indice primario de la tabla principal ha de ser un índice primario (PRIMARY KEY) de la tabla principal. Debe haber plena coincidencia (tanto en tipos como contenidos) entre ambos índices.

<?php
$tabla1="principal";
$tabla2="vinculada";
/*incluimos los parámetros de conexión */
include('mysqli.inc.php');

 /******    Programación mediante procesos ***********/
    #conexion, selección de base de datos y verificacion de errores segun ejemplos anteriores
$conexion=@mysqli_connect ($cfg_servidor,$cfg_usuario,$cfg_password,$cfg_basephp1);
# creación de la tabla principal type InnoDB
$crear="CREATE TABLE IF NOT EXISTS $tabla1 (";
$crear.="DNI CHAR(8) NOT NULL,  ";
$crear.="Nombre VARCHAR (20)  NOT NULL, ";
$crear.="Apellido1 VARCHAR (15)  not null, ";
$crear.="Apellido2 VARCHAR (15)  not null, "; 
# el indice primario es imprescindible. Recuerda que debe
# estar definido sobre campos NO NULOS
$crear.=" PRIMARY KEY(DNI) ";
$crear.=")";
$crear.=" Type=InnoDB";
# creamos la tabla principal comprobando el resultado
if(@mysqli_query ($conexion,$crear)){
	print "La tabla ".$tabla1." ha sido creada<br>";
}else{
	print "No se ha creado ".$tabla1." ha habido un error<br>";
}
# crearemos la tabla vinculada
$crear1="CREATE TABLE IF NOT EXISTS $tabla2 (";
$crear1.="IDENTIDAD CHAR(8) NOT NULL, ";
$crear1.="calle VARCHAR (20), ";
$crear1.="poblacion VARCHAR (20), ";
$crear1.="distrito VARCHAR(5), ";
# creamos el índice (lo llamamos asociador) para la vinculación
# en este caso no será ni primario ni único
# Observa que el campo IDENTIDAD de esta tabla CHAR(8)
# es idéntico al campo DNI de la tabla principal
$crear1.=" KEY asociador(IDENTIDAD), ";
#establecemos la vinculación de ambos índices
$crear1.=" FOREIGN KEY (IDENTIDAD) REFERENCES $tabla1(DNI) ";
$crear1.=") TYPE = INNODB";
#  creamos (y comprobamos la creación) la tabla vinculada
if(@mysqli_query ($conexion ,$crear1)){
	print "La tabla ".$tabla2." ha sido creada<br>";
}else{
	print "No se ha creado ".$tabla2." ha habido un error<br>";
}
mysqli_close($conexion);
?>

Aquí tienes el ejemplo en ambas modalidades

Ejecutar
Modo procesos
Ver código fuente
Modo procesos
Ejecutar
Orientado a objetos
Ver código fuente
Orientado a objetos

Modificación de estructuras

La modificación de estructuras en tablas vinculadas puede hacerse de forma idéntica a la estudiada para los casos generales de MySQL siempre que esas modificaciones no afecten a los campos mediante los que se establecen las vinculaciones entre tablas.

Aquí tienes un ejemplo en se borran y añaden campos en ambas tablas. Como puedes ver la sintaxis es exactamente la misma que utilizamos en temas anteriores.

Ver código fuente
Modo procesos
Ver código fuente
Orientado a objetos

Modificación o borrado de campos vinculados

Las sentencias MySQL que deban modificar o eliminar campos utilizados para establecer vínculos entre tablas requieren de un parámetro especial (CONSTRAINT) –puede ser distinto en cada una de las tablas– que es necesario conocer previamente.

La forma de visualizarlo es ejecutar la sentencia: SHOW CREATE TABLE nombre tabla que devuelve como resultado un array asociativo con dos índices. Uno de ellos -llamado Table- que contiene el nombre de la tabla y el otro -Create Table- que contiene la estructura con la que ha sido creada la tabla, pero incluyendo el parámetro CONSTRAINT seguido de su valor. Ese valor es precisamente el que necesitamos para hacer modificaciones en los campos asociados de las tablas vinculadas.

En estos podrás ver el código fuente y ejecutar los scripts que permiten visualizar el valor de de CONSTRAINT.

Ejecutar
Modo procesos
Ver código fuente
Modo procesos
Ejecutar
Orientado a objetos
Ver código fuente
Orientado a objetos

Conocido el valor de parámetro anterior el proceso de borrado del vínculo actual requiere la siguiente sintaxis:

ALTER TABLE nombre de la tabla DROP FOREIGN KEY parametro

Cuando se trata de añadir un nuevo vínculo con una tabla principal habremos de utilizar la siguiente sentencia:

ALTER TABLE nombre de la tabla ADD [CONSTRAINT parametro] FOREIGN KEY parametro REFERENCES tabla principal(clave primaria)

El parámetro CONSTRAIT (encerrado en corchetes en el párrafo anterior) es OPCIONAL y solo habría de utilzarse en el caso de que existiera ya una vinculación previa de esa tabla.

En estos ejemploS determinaremos el valor de CONSTRAINT borraremos un campo asociado de una tabla vinculada y posteriormente crearemos un nuevo campo idéntico al eliminado.

Ejecutar
Modo procesos
Ver código fuente
Modo procesos
Ejecutar
Orientado a objetos
Ver código fuente
Orientado a objetos

Añadir registros a la tabla vinculada

Agregar nuevos registros a una tabla vinculada como la de los ejemplos anteriores no requiere ningún tratamiento especial. El proceso es ya comentado en páginas anteriores. El único problema que podría plantearse sería cuando se intentará añadir un registro y se produjera un error nº 1452. Cannot add or update a child row: a foreign key constraint fails (`ejemplos`.`vinculada`, CONSTRAINT `vinculada_ibfk_1` FOREIGN KEY (`IDENTIDAD`) REFERENCES `principal` (`DNI`)). Esto ocurrirá si intentamos añadir un registro a la tabla vinculada sin que en el campo DNI de la tabla principal exista otro registro con un valor igual al que pretendemos introducir en la tabla vinculada.

Hemos de añadir, y por este orden, un registro a la tabla principal con el DNI requerido y posteriormente ya podremos ejecutar -sin errores- el script que inserta datos en la tabla vinculada. Y podremos ejecutarlo tantas veces como queramos ya que -el campo IDENTIDAD está definido como KEY y por tanto permite duplicados- no hemos establecido la condición de índice PRIMARIO ó UNICO.

Borrar o modificar registros en la tabla principal

El borrado de registros también requiere ejecutarse en el orden adecuado. En este caso hemos de empezar borrando los registros de la tabla vinculada para, posteriormente, hacerlo con la tabla principal. Sin intentamos borrar un registro de la tabla principal antes de eliminar todos los vinculados a él se producirá un error nº 1451 que corresponde a: Cannot delete or update a parent row: a foreign key constraint fails (`ejemplos`.`vinculada`, CONSTRAINT `vinculada_ibfk_1` FOREIGN KEY (`IDENTIDAD`) REFERENCES `principal` (`DNI`)) advirtiéndonos de que no se realiza el borrado porque existen registros en la tabla vinculada con valores asociados al índice del campo que pretendemos borrar y, de permitir hacerlo, se rompería la integridad referencial ya que quedarían registros huérfanos en la tabla vinculada.

Lo mismo ocurirá sin tratamos de modificar un registro de la tabla principal y la modificación afecta al índice que realiza la asociación con la tabla (o tablas) vinculadas se produciría -por las mismas razones y en las mismas circunstancias- un error 1451 que corresponde a: Cannot delete or update a parent row: a foreign key constraint fails (`ejemplos`.`vinculada`, CONSTRAINT `vinculada_ibfk_1` FOREIGN KEY (`IDENTIDAD`) REFERENCES `principal` (`DNI`)) que impediría la modificación.

El borrado o modificación de un registro de la tabla principal requiere siempre que no existieran en la tabla (o tablas) vinculada registros asociados con él.

Borrado de tablas vinculadas

Si pretendemos eliminar una tabla principal sin haberlo hecho previamente con sus tablas vinculadas recibiremos un mensaje de error y no se producirá el borrado.

Las tablas vinculadas sí permiten el borrado y una vez que éstas ya han sido eliminadas (o quitada la vinculación) ya podrán borrarse sin problemas las tablas principales. El orden de borrado (si ambas pretenden borrarse desde el mismo script)es importante. Primero se borra la vinculada y luego la principal.

Opciones adicionales de FOREIGN KEY

La claúsula FOREIGN KEY permite añadirle –detrás de la definición ya comentada y sin poner coma separándola de ella– los parámetros ON DELETE y ON UPDATE en las que se permite especificar una de las siguientes opciones:

ON DELETE RESTRICT

Esta condición (es la condición por defecto de MySQL y no es preciso escribirla) indica a MySQL que interrumpa el proceso de borrado y dé un mensaje de error cuando se intente borrar un registro de la tabla principal cuando en la tabla vinculada existan registros asociados al valor que se pretende borrar.

ON DELETE NO ACTION

Es un sinónimo de la anterior.

ON DELETE CASCADE

Cuando se especifica esta opción, al borrar un registro de la tabla principal se borrarán de forma automática todos los de la tabla vinculada que estuvieran asociados al valor de la clave foránea que se trata de borrar. Con ello se conseguiría una actualización automática de la segunda tabla y se mantendría la identidad referencial.

ON DELETE SET NULL

Con esta opción, al borrar el registro de la tabla principal no se borrarían los que tuviera asociados la tabla secundaria pero tomarían valor NULL todos los índices de ella coincidentes con la clave primaria de la tabla principal.

Para el caso de ON UPDATE las opciones son estas:

ON UPDATE RESTRICT
ON UPDATE CASCADE
ON UPDATE SET NULL

Su comportamiento es idéntico a sus homónimas del caso anterior.

  ¡Cuidado!  

El uso de la opción SET NULL requiere que el campo indicado en FOREIGN KEY permita valores nulos. Si está definido con flag NOT NULL (como ocurre en los ejemplos que tienes aquí) daría un mensaje de error.

Al incluir ON DELETE y ON UPTADE (si se incluyen ambas) han de hacerse por este mismo orden.
Si se cambiara este orden daría un mensaje de error y no se ejecutarían.

Automatización de procesos

En estos ejemplos haremos un proceso partiendo de cero. Borraremos las eventuales tablas preexistentes, crearemos dos tablas vinculadas, insertaremos algunos datos y posteriorme realizaremos una actualización en cascada.

Ejecutar
Modo procesos
Ver código fuente
Modo procesos
Ejecutar
Orientado a objetos
Ver código fuente
Orientado a objetos