[SOLUCIONADO] DELETE de múltiples entradas en distintas tablas

[guardarmicorreo]

Tengo tres tablas:

categories

Código SQL:

CREATE TABLE `categories` (
 `idcategorie` INT(11) NOT NULL AUTO_INCREMENT,
 `iduser` INT(11) DEFAULT NULL,
 `categorie` VARCHAR(45) COLLATE utf8_spanish2_ci DEFAULT NULL,
 PRIMARY KEY (`idcategorie`),
 KEY `iduser_idx` (`iduser`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci COMMENT='categorias principales. esta relacionada con subcategorias.'
 

subcategories

Código SQL:

CREATE TABLE `subcategories` (
 `idsubcategorie` INT(11) NOT NULL AUTO_INCREMENT,
 `idcategorie` INT(11) DEFAULT NULL,
 `subcategorie` VARCHAR(45) COLLATE utf8_spanish2_ci DEFAULT NULL,
 PRIMARY KEY (`idsubcategorie`),
 KEY `fk_idcategorie_idx` (`idcategorie`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci COMMENT='sirve para clasificar los posts. subcategorias tiene clave foranea de la clave primaria de posts'
 

posts

Código SQL:

CREATE TABLE `posts` (
 `idpost` INT(11) NOT NULL AUTO_INCREMENT,
 `author` VARCHAR(45) COLLATE utf8_spanish2_ci DEFAULT NULL,
 `date_time` datetime DEFAULT NULL,
 `title` VARCHAR(45) COLLATE utf8_spanish2_ci DEFAULT NULL,
 `post` mediumtext COLLATE utf8_spanish2_ci,
 `conclusion` mediumtext COLLATE utf8_spanish2_ci,
 `footer` VARCHAR(45) COLLATE utf8_spanish2_ci DEFAULT NULL,
 `idsubcategorie` INT(11) DEFAULT NULL,
 PRIMARY KEY (`idpost`),
 FULLTEXT KEY `post_index` (`post`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci
 

El problema es que no puedo utilizar ON DELETE CASCADE para poder borrar registros en cascada de manera que pueda mantener la coherencia de los datos en la BD.

El motivo de esto es que posts no es InnoDB, sino MyIsam; esto se debe a que nesito que el campo post tenga FULLTEXT para poder realizar búsquedas profundas en su contenido.

No logro hacer que se borre una categorie junto con las subcategories relacionadas y a la vez los posts relacionados con subcategories.

¿Alguna idea de cómo podría hacer esto? He leído el manual (en inglés y español), he puesto en práctica sus ejemplos, pero no me sirven.

[gnzsoloyo]

Si son tablas MySQM, tendrás que hacer tres DELETEs diferentes, no hay otra forma. Y no te conviene hacerlo por TRIGGERS, por lo que no te aconsejo intentarlo de ese modo.
Lo que yo te sugeriría es que lo hagas todo dentro de un stored procedure, donde puedes hacer cada etapa de validación necesaria para controlar la consistencia de datos.
Es decir, antes de borrar una tabla, recuperas el registro entero. Si falla una de cualquiera de ellas, reinsertas lo recuperado y cancelas la operación.
¿se entiende?

[quimfv]

Qual es tu problema la secuencia de querys o encontrar un sistema automatico alternativo?

si es lo segundo creo que no hay....

[guardarmicorreo]

Gracias gnzsoloyo!

Verás, por store procedure no sé cómo se podría hacer, ya que soy muy noob en esto de SQL Y MYSQL.

Yo voy ejecutando consultas según vaya necesitando.

Ante ese panorama, lo que pretendo es borrar de una sola consulta:

categories, a su vez las subcategories cuyo idcategorie foreign key coincida con el idcategorie de categories y a su vez los posts relacionados con la subcategorie.

Lo que me has explicado lo veo genial, pero no sé hacerlo, por ello, si es la solución, entonces tendré que aprender triggers y store procedure, que de todas formas tendría que hacerlo para mejorar mi dominio en SQL y MYSQL.

Aunque intuyo que lo que me has dicho sería para aumentar la seguridad en las consultas ¿no?

muchas gracias!!! :D

quimfv gracias!

mi problema es que no sé cómo podría construir el SQL del query para poder borrar múltiples entradas de múltiples tablas.

[quimfv]

No se puede construir una sola query que haga lo que pides, se podría como bien sabes si dispusiéramos del ON DELETE CASCADE, solo con borrar las categorías se borraría todo lo demás....

La solución del stored, procedimiento almacenado, consiste en que programas una pequeña rutina que ejecute las querys una detrás de la otra para conseguir lo buscado, lo que te propone @gnzsoloyo es que antes de borrar guardes los datos en tablas temporales para poder recuperarlos si una de las querys falla, restaurando así la situación, como si dispusiéramos de TRANSACCIONES.

Se llama "procedimiento almacenado" por que se programa con lenguaje propio del servidor de bases de datos, tienes dos opciones aprender el lenguaje y programar el procedimiento, o usar un lenguaje externo para programar un procedimiento que haga lo mismo solo que no será stored (almacenado).

[guardarmicorreo]

Sin duda prefiero aprender cómo crear un stored procedure y triggers para hacerlo correctamente, aunque me lleve más tiempo. si puedo evitar hacerlo de forma chapuza, mejor que mejor!! :D

Muchas gracias a ambos por su ayuda!! fue vital y relevante para mi saber esto, porque creía que se podía hacer con una sola consulta.

Saludos!

[gnzsoloyo]

Vamos a hacer un intento, a ver si funciona:

Código MySQL:

DELETE a, b, c
FROM posts a INNER JOIN subcategories b ON a.idsubcategorie = b.idsubcategorie
INNER JOIN categories c ON b.idcategorie = c.idcategorie
WHERE c.idcategorie = valorIdABorrar
 

En teoría debería poder hacer el DELETE siendo tablas MyISAM. No funciona bien con las InnoDB porque MySQL no lee previamene la cadena de dependencias.
A mi no me gusta la forma porque si falla uno de los borrados de las tablas no hay rollback.

Un ejemplo de mi idea respecto al SP sería mas o menos así:

Código MySQL:

CREATE PROCEDURE borrar(IN idcat INT)
BEGIN
    CREATE TEMPORARY TABLE posts_temp
    SELECT * FROM posts
    WHERE idsubcategory IN 
        (SELECT idsubcategory 
           FROM subcategories a 
                INNER JOIN categories c ON s.idcategorie = c.idcategorie 
          WHERE c.idcategorie = idcat);
    DELETE posts
    WHERE idsubcategory IN 
         (SELECT idsubcategory 
            FROM subcategories a 
                 INNER JOIN categories c ON s.idcategorie = c.idcategorie 
           WHERE c.idcategorie = idcat);
    IF ROW_COUNT()  = 0 THEN
        REPLACE posts
        SELECT *
        FROM posts_temp;
        SELECT 'Fallo al borrar posts.' resultado;
    ELSE
        CREATE TEMPORARY TABLE subcat_temp
        SELECT * FROM subcategories
           WHERE idcategory = idcat;
        DELETE subcategories
         WHERE idcategory = idcat;
        IF ROW_COUNT() = 0 THEN
            REPLACE posts
            SELECT *
            FROM posts_temp;
            REPLACE subcategories
            SELECT *
            FROM subcat_temp;
            SELECT 'Fallo al borrar subcategorias.' resultado;
        ELSE
            CREATE TEMPORARY TABLE cat_temp
            SELECT * FROM categories
               WHERE idcategory = idcat;
            DELETE categories
             WHERE idcategory = idcat;
            IF ROW_COUNT() = 0 THEN
                REPLACE posts
                SELECT *
                FROM posts_temp;
                REPLACE subcategories
                SELECT *
                FROM subcat_temp;
                SELECT 'Fallo al borrar categoria' resultado;
            ELSE
                SELECT 'Borrado exitoso' resultado;
            END IF;            
        END IF ;
    END IF;
END
 

Aclaro que no lo he podido probar, por lo que puede contener muchos errores, y no sé si las versiones de MySQL posteriores a la 5.1.x soportan ROW_COUNT() .
Como nota, estoy haciendo que devuelva un result de mensaje, por lo que deberás recuperarlo al ejecutar el SP

[guardarmicorreo]

WOW! W-O-W!

Para empezar que el primer SQL query que pusistes me funcionó a la primera, tal cual.

Y para terminar

Me dejastes de piedra con semejante SQL :D

En mi vida había visto un SQL tan largo, si eso es un SP entonces creo que tengo para aprender durante un buen rato jajajaja

Simplemente genial gnzsoloyo!!! muchas gracias!!! tomaré como referencia tu SP para aprender algo más rápido, de verdad que me es de mucha ayuda.

Gracias!!! :D

[gnzsoloyo]

En mi vida había visto un SQL tan largo,

¿Largo?
Noooo... El más largo que hice tiene mas o menos unas 580 líneas, y algunas decenas de pasos.
A pesar de que la mayoría piensa que un Sp es para contener una sentencia simple, el sentido de un SP es principalmente hacer secuencias programadas de más de una sentencia como una sola tarea.

[guardarmicorreo]

Pfff, es realmente complejo ya que como dijistes una vez, y no se me olvida, cada acción en MYSQL debe ser revisada con el manual porque cada sintaxis es de una manera muy concreta.

Realmente gracias! :D ahora mismo estoy con un curso de system administrator de red hat, pero en cuanto termine, empezaré de lleno con SP y triggers.

Me encantan las BD en MYSQL :P