Handling Foreign key errors in Codeigniter

Question

I am implementing a code where there is a foreign key in two tables. Soon I tried several means and could not handle the error:

Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails (`base`.`tableus`, CONSTRAINT `doctor_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`))

DELETE FROM `department` WHERE `department_id` = '1'

Filename: models/Crud.php

Line Number: 292

I’ve used an example I found Here and did not resolve.

I revising my question here, I missed an important detail, I do not want the user to delete the data from the table 'father' while having tables 'daughters' linked to it. Every time the user tries to do this he should get an error, in case I’m not able to handle this error in codeigniter.

    function delete_department($department_id)
{
  //verifica se recebe mensagem de erro na exclusão.

    $verifica_erro = $this->db->_error_number() == 1451;
    if ($this->delete_department($department_id) == $verifica_erro){
        echo "Mensagem de erro para o usuário!";
    }else{ 

 //senão tiver executa a exclusão.
        $this->db->where('department_id',$department_id);
        $this->db->delete('department');
    }
}

This code executes and brings another error:

Fatal error: Uncaught Typeerror: Argument 1 passed to Ci_exceptions::show_exception() must be an instance of Exception, instance of Error Given, called in /Path/system/core/Common.php on line 658 and defined in /Path/system/core/Exceptions.php:190 Stack trace: #0 /Path/system/core/Common.php(658): Ci_exceptions->show_exception(Object(Error)) #1 [Internal Function]: _exception_handler(Object(Error)) #2 {main} in thrown /Path/system/core/Exceptions.php on line 190

Answer 1

This is a problem with your database configuration. Modeling is an important thing to avoid these things. This and knowing the application documentation.

Mysql does this to prevent data integrity from being compromised, that is, to prevent you from changing a main record and making all other references to this record go missing. See, the mysql documentation says (in free translation):

When an UPDATE or DELETE operation affects a key value in the parent table that has corresponding rows in the child table, the result depends on the referential action specified using the ON UPDATE and ON DELETE subclades of the FOREIGN KEY clause. Mysql supports five options on the action to be taken, listed here:

Basically, you have to tell the database to automatically remove/update in the child tables the deleted/updated records in the main table. That’s what you should use CASCADE, as we saw above:

CASCADE: Delete or update the parent table row and automatically delete or update the corresponding rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several clauses ON UPDATE CASCADE that act in the same column in the parent table or in the child table.

An example of a link with automatic deletion/update of references

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=INNODB;

Viu ON DELETE CASCADE and ON UPDATE CASCADE? It is. This configuration in the table avoids this error that you are seeing. So when the id is changed/deleted in the table parent, parent_id will be automatically changed and deleted in child tables.

Heed: using CASCADE is a good habit to copy the records before deleting/updating. So, if necessary, it will be possible to retrieve the data for conference.

Answer 2

After editing the question became clearer, and the answer is simple: there’s no way to treat this mistake the way you want it. The way you make the mistake is already being treated.

Think: there is a FK, and she’s doing her job. So if you try to delete or update a record Parent unused CASCADE, you will get the error, and this can not be avoided in the PHP, because it happens in the bank, and the Codeigniter there is no way to prevent mistakes from happening in the bank. At most it will give you a warning when they happen, and that is what it is doing.

However, it is possible to test the records before executing the exclusion attempt, and that’s simple:

Consider that parent is its main table, and that child is your table that has the record parent_id related.

function delete($id = NULL){
        $query = "DELETE FROM parent WHERE id = '$id';";
        # The cat's leap: teste as tabelas antes de tentar excluir o principal
        $this->db->where('parent_id', $id);
        $test = $this->db->get('child');
        if(empty($test->result_array())){
            echo 'execute $query';
            //$this->db->query($query);
        } 
        else {
            echo 'show some error';
        }
}

All this just because you don’t want to use CASCADE.

Answer 3

You first have to delete the other record from the table that this table is referencing through the foreign key.