Deletion with Referential Integrity MYSQL

Navigation
0

The project world is as follows:

I have a client, where I register for it, messages, news, upload photos, where there are calendars as well. So when I'm about to delete it, I need to delete everything from it, because the database does not allow for referential integrity. So far so good, but in the news section, I have another table that links the news images to that news, and this link is made by a table where I have the code of the news and the code of the image, in fact it is a link many For many. So, there is the problem, for me to delete the field of the table images related to that news is necessary that I delete before this relationship between the image and the news, but if I delete, I can not know what publication is that image .

How can I resolve this?

Follow what I've been doing 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);
    }
  

You need to follow a deletion order and it is: Deleting Schedule, Deleting ImagexPublishing, Deleting Image, Deleting Publication, Deleting Comments, Deleting client_Pf and last Deleting client.

An error is occurring when I delete the Publications / Images table, it reports 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 '.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')
    
asked by anonymous 14.07.2015 / 13:17

1 answer

1

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

Here is a small 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 definition, when a client is deleted, all tables that depend on the tbl_CLIENTES table (have a foreign key that refers to a column in the tbl_CLIENTES table) 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 content of the tables is currently: 

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 delete one of the clients 

delete from tbl_CLIENTES where idCliente = 1

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

select * from tbl_clientes_agenda; 

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

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

    
14.07.2015 / 15:19
pass actionlink parameters to jquery function WebView does not want to go to the center