DB | SQL - How to allow the removal of used foreign keys ?

Question

Hi there, I'm working on a project right now, and I've' just figured out I have some troubles when it comes to delete foreign keys being used in my application.

To details this, here are some details. Basically I have two tables, one called t_bugs and another one called t_devs.

I my t_bugs table, there is simply a column called FK_dev_ID which references the ID of some dev assigned to the bug.

If a dev isn't assigned to any bug, I can delete him whitout any problem with this command for example.

DELETE FROM t_devs WHERE ID_dev = 2

However, if the dev with the id of 2 is already assigned to some bugs to solve, if I want to run the command above, it won't work, I'll get an error.

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`pangolin`.`t_bugs`, CONSTRAINT `t_bugs_ibfk_2` FOREIGN KEY (`FK_dev_ID`) REFERENCES `t_devs` (`ID_dev`))

It's somewhat logical, now, I would like to know how to be able to delete a user even if he is already assigned to some bugs, in other words, even if Its ID is already referenced as a foreign key in another table.

If ever I find a solution in the meantime, I'll post this one here.

Answer 1 · 2015-01-19T15:10:25Z

January 19, 2015 3:10pm

yes shez azr I've finally managed to solve this little concern the way we discussed it. It uses several queries, It works, but there is probably a solution in SQL that could do all of this in a single query, at least I guess, I was told several times that SQL was very, very powerful... I'm unfortunately not enough skilled with it to do it this way.

For those interested, we never know, here is how I dealt with it.

I have in fact a controller.php file in my project where I define all my queries, in this file I created 4 differents queries to be able to delete a label which is already assigned to some bugs.

Here are these queries:

<?php

// I use this to see if the label I want to delete is used

function count_label($ID_label){

    include(ROOT_PATH."inc/database.php");

    $results = $bdd->prepare("SELECT COUNT(FK_label_ID) FROM t_bugs WHERE FK_Label_ID = ? LIMIT 1;");
    $results->bindParam(1,$ID_label);
    $results->execute();

    $count_used_labels = $results->fetch();
    $count = $count_used_labels[0];

    return $count;
}


// If this label is used, I want to get the ID of these bugs
// where the label is used, I get an array.

function get_bugs_where_label_used($ID_label){

    include(ROOT_PATH."inc/database.php");

    $results = $bdd->prepare("SELECT Id_bug FROM t_bugs WHERE FK_label_ID = ?;");
    $results->bindParam(1,$ID_label);
    $results->execute();

    $bugs = $results->fetchAll(PDO::FETCH_COLUMN);

    return $bugs;

}

//Once I have these bugs where the label is used identified, 
//I set their label value to 0, 
//which means inside my app "not yet assigned

function update_label_to_na($ID_bug){

    include(ROOT_PATH."inc/database.php");

    $results = $bdd->prepare("UPDATE t_bugs SET FK_label_ID = 0 WHERE ID_bug = ?;");
    $results->bindParam(1,$ID_bug);
    $results->execute();

}

//As the label isn't used anymore, I can now 
//delete this one safely without any errors.

function delete_targeted_label($ID_label){

    include(ROOT_PATH."inc/database.php");

    $results = $bdd->prepare("DELETE FROM t_labels WHERE ID_label = ?;");
    $results->bindParam(1,$ID_label);
    $results->execute();
}
?>

Then I just created a test.php file to test my "workflow".

<?php

require_once("inc/config.php");
include(ROOT_PATH."inc/controller.php");

$idlabel = 14;
$labels = count_label($idlabel);



if($labels>0){

    $id_bugs = get_bugs_where_label_used($idlabel);

    foreach($id_bugs as $id_bug){
        echo $id_bug.'<br>';
        update_label_to_na($id_bug);
    }

    delete_targeted_label($idlabel);

    echo "it works";

}else{
    echo "not above 0";
}
?>

Sorry for the length of the post, coudln't do shorter.

Thank you to Ken Alger as well, I didn't know what you told me, that's something I didn't know. I tested it as well. That could be handy in some situations.

Answer 2 · 2015-01-16T16:53:39Z

January 16, 2015 4:53pm

you could do a check on the other table to see if what you are deleting is being referred & delete it there first before doing your delete

OR like mentioned above set the cascade delete (google it). what this will do is when you delete users table, it will delete all the rows of other tables where it is referenced

Answer 3 · 2015-01-16T18:18:51Z

January 16, 2015 6:18pm

Thanks for you answers, I'm going to check all things you told me. However from what I read, according your proposals, in both cases, It's going to delete the whole row of the bug where the foreign key (thus in this case the dev assigned to the bug) is referenced.

I'm going to give you an example of the behaviour I want , let's say I have an html table referencing all the bugs, each row of the table represent a bug, each bug has a dev assigned in a column, this dev is in fact referenced thanks a foreign key.

Let's say I delete in my t_devs table, the dev with the id of 2 thanks a SQL query. At the same time, this dev is being used multiple times in the HTML table (in the table t_bugs as well as foreign key so) because he is being assigned to several bugs...

From what I understand with the technics you told me, that could be done, but the bugs where this dev was assigned are about to be deleted as well... So the entire row is going to disappear from the HTML table, I don't want it.

I don't really want this kind of behaviour, I would like the dev to be deleted from the t_devs table, and all the bugs where this dev was assigned still to be alive... I don't want them to be deleted.

Insteald I would like to have in my HTML table something like "not yet assigned, n/a" instead of the name of the dev previously assigned.

How this could be feasible ?

What I have in mind is maybe just as shez azr stated check the t_bugs table to see if the dev is being referenced, if so run a query such as an update that would set the foreign key of the dev referenced to all bugs with a null value, and then only delete the dev, am I on the right path ?

I just have to check how to play with several queries at once.

Answer 4 · 2015-01-17T20:35:17Z

January 17, 2015 8:35pm

hm.. in that case you don't want delete cascade.. this should only be used when the data in table will become useless without a foreign key. For example if you have users table & users_hobbies table. If you delete a user, that user's hobbies info will be rendered useless.

Back to your question: When you delete a user (assuming there's an admin interface, where you click on user's name to delete it which is a link with user_id prepended) you delete the user & then just update the bugs table something like:

update bugs_table, set dev_id = null where dev_id = user_id

Welcome to the Treehouse Community

Looking to learn something new?

geoffrey

geoffrey

DB | SQL - How to allow the removal of used foreign keys ?

Ken Alger

Ken Alger

4 Answers

geoffrey

geoffrey

shezazr

shezazr

geoffrey

geoffrey

shezazr

shezazr