Deletion with MYSQL reference integrity

Question

The world of design is as follows:

I have a client, where I register for him, errands, news, upload photos, where there are schedules too. Only when I’m about to delete it, I need to delete everything from it, because the bank won’t allow it for referential integrity. So far so good, but in the news section, I have another table that links the images of the news to that news, and this link is made by a table where I have the news code and the image code, in fact it is a link many to many. So that is the problem, for me to delete the field of the table images referring to that news it is necessary that I delete before this relationship between the image and the news, but if I delete, I am not able to know what publication is that image.

How can I fix this ?

Follow what I’ve done so far.

        mysql_query("DELETE FROM tbl_CLIENTES_AGENDA WHERE COD_IDENT_CLIEN ='" . $COD_IDENT_CLIEN . "'");

    mysql_query("DELETE FROM tbl_IMAGENS
                            WHERE COD_SEQUN_IMAGM IN 
                            (
                                    SELECT COD_SEQUN_IMAGM FROM tbl_PUBLICACOESxIMAGENS
                                WHERE COD_IDENT_PUBLI IN
                                (
                                            SELECT COD_IDENT_CLIEN FROM tbl_PUBLICACOES
                                    WHERE COD_IDENT_CLIEN = " . $COD_IDENT_CLIEN . "
                                )
                            )");
    mysql_query("DELETE FROM tbl_PUBLICACOES WHERE COD_IDENT_CLIEN = '" . $COD_IDENT_CLIEN . "'");
    mysql_query("DELETE FROM tbl_RECADOS WHERE COD_IDENT_CLIEN = '" . $COD_IDENT_CLIEN . "");
    mysql_query("DELETE FROM tbl_CLIENTES_PF WHERE COD_IDENT_CLIEN ='" . $COD_IDENT_CLIEN . "'");
    mysql_query("DELETE FROM tbl_CLIENTES WHERE COD_IDENT_CLIEN ='" . $COD_IDENT_CLIEN . "'");
    $query = mysql_query("SELECT * FROM tbl_IMAGENS I INNER JOIN tbl_CLIENTESxIMAGENS CI ON I.COD_SEQUN_IMAGM = CI.COD_SEQUN_IMAGM WHERE COD_IDENT_CLIEN ='" . $COD_IDENT_CLIEN . "' ");
    while ($linha2 = mysql_fetch_array($query)) {
        $arquivo = "../uploads/{$linha2['TXT_FILEN_IMAGN']}";
        unlink($arquivo);
    }
    mysql_query("DELETE FROM tbl_CLIENTESxIMAGENS WHERE COD_IDENT_CLIEN ='" . $COD_IDENT_CLIEN . '"');
    mysql_query("DELETE FROM tbl_IMAGENS WHERE COD_SEQUN_IMAGM IN
(
    SELECT COD_SEQUN_IMAGM FROM tbl_CLIENTESxIMAGENS WHERE COD_IDENT_CLIEN = '" . $COD_IDENT_CLIEN . "'
)");

    $sql = mysql_query("SELECT C.COD_IDENT_CLIEN, P.COD_IDENT_PUBLI, I.COD_SEQUN_IMAGM, I.TXT_FILEN_IMAGN
                                        FROM
                                        (
                                        SELECT * FROM tbl_CLIENTES WHERE COD_IDENT_CLIEN = '" . $COD_IDENT_CLIEN . "'
                                        ) C 

                                        LEFT JOIN
                                        (
                                        SELECT * FROM tbl_PUBLICACOES
                                        ) P
                                        ON P.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN

                                        LEFT JOIN
                                        (
                                        SELECT * FROM tbl_PUBLICACOESxIMAGENS
                                        ) PI
                                        ON PI.COD_IDENT_PUBLI = P.COD_IDENT_PUBLI

                                        LEFT JOIN
                                        (
                                        SELECT * FROM tbl_IMAGENS
                                        ) I
                                        ON I.COD_SEQUN_IMAGM = PI.COD_SEQUN_IMAGM  WHERE I.COD_SEQUN_IMAGM is not NULL;

                                        ");
    while ($linha = mysql_fetch_array($sql)) {
        $arquivo = "../uploads/{$linha['TXT_FILEN_IMAGN']}";
        unlink($arquivo);
    }

It is necessary to follow an order for deletion and it is: Delete Calendar, Delete Imagexpublication, Delete Image, Delete Publication, Delete Scrapbook, Delete cliente_Pf and Delete client last.

An error is occurring when I delete the Post table Ximages, it accuses the following error:

11:18:57 DELETE FROM tbl_PUBLICACOESxIMAGENS WHERE COD_SEQUN_IMAGM IN ('64,65,66,67,68,70,71') 1 Row(s) affected, 1 Warning(s): 1292 Truncated incorrect DOUBLE value: '64,65,66,67,68,70,71' 0.374 sec

The query I made is as follows::

DELETE FROM tbl_PUBLICACOESxIMAGENS WHERE COD_SEQUN_IMAGM IN ('64,65,66,67,68,70,71')

Answer 1

As indicated in the comments you do not need to do this management manually. If your goal is to delete the logs from all tables you can use the option ON DELETE CASCADE.

Here’s a short example using two of your tables.

create table tbl_CLIENTES (
    idCliente bigint(20) unsigned NOT NULL
  , loginCliente varchar(60) NOT NULL default ''
  , nome varchar(250) NOT NULL default ''
  , PRIMARY KEY  (idCliente)
  , KEY `user_login_key` (loginCliente)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

create table tbl_CLIENTES_AGENDA 
(
    id  int unsigned NOT NULL
  , idCliente   bigint(20) unsigned NOT NULL
  , email varchar(60) NOT NULL default ''
  , PRIMARY KEY  (id)
  , FOREIGN KEY (idCliente) REFERENCES tbl_CLIENTES(idCliente) 
    ON UPDATE CASCADE ON DELETE CASCADE
)Engine=INNODB;

With this setting, when a client is deleted, all tables that depend on the table tbl_CLIENTES (have a Foreign key that refers to a column in the table tbl_CLIENTES) will be automatically updated, that is, the records corresponding to that client will be deleted as well.

insert into tbl_CLIENTES values
(1, 'manuel00', 'Manuel Silva'),
(2, 'maria', 'Maria Silva');

insert into tbl_CLIENTES_AGENDA values
(1, 1, '[email protected]'),
(2, 2, '[email protected]');

The contents of the tables are now:

select * from tbl_clientes;

idCliente    | loginCliente  | nome
1            | manuel        | Manuel Silva
2            | maria         | Maria Silva 

select * from tbl_clientes_agenda;    

id     |  idCliente  | email
1      |  1          | [email protected]
2      |  2          | [email protected] 

Now we erase one of the customers

delete from tbl_CLIENTES where idCliente = 1

And after that the contents of table tbl_CLIENTES_AGENDA is as follows:

select * from tbl_clientes_agenda; 

id     |  idCliente  | email
2      |  2          | [email protected] 

Now, just apply this concept to all your tables that have Foreign Key.