Delete Query constraint fail

Question

I'm creating an ADD/UPDATE/DELETE system for my database, and while coding the delete query I've found a constraint fails, preventing me from deleting certain data from a specific row.

Delete query:

if (isset($_GET['company'])) {
    $id        = $_GET['company'];
    $query_del = " DELETE company.* , client.* , queries.*
            FROM company
            INNER JOIN client ON company.idcompany =
            client.idcompany
            INNER JOIN queries ON
            client.idclient =
            queries.idclient
            WHERE company.idcompany = '" . $id . "'";
    $result_del = mysqli_query($con, $query_del);
    if ($result_del) {
        header("location:companylist.php");
    } else {
        echo ' Erro: ' . mysqli_error($con);
    }
}

Tables required:

company: idcompany , company_name 
client: idclient , idcompany
queries: idclient

Error:

Cannot delete or update a parent row: a foreign key constraint fails (db.client, CONSTRAINT client_ibfk_1 FOREIGN KEY (idcompany) REFERENCES company (idcompany))

Basically, I'm getting the row number through URL and trying to delete all the data related with that idcompany on all three tables. Does somebody know how I can relate all three tables on a delete query?

You should alter your foregin key to be `ON DELTE CASCADE`, then you only have to delete from the primary table, and the rest will follow. — Qirel, Jul 01 '19 at 10:47
The weird query for **DELETE**, it feels like **SELECT** query. — Rahul, Jul 01 '19 at 10:47
Also, `DELETE company.* , client.* , queries.*` is incorrect - you don't delete from columns - only tables, so it should be something like `DELETE company, client, queries` — Qirel, Jul 01 '19 at 10:47
If table `A` points to table`B` with foreign key `constraint` you can't deleted it. You have to delete parent table record first. Read this answer https://stackoverflow.com/questions/757181/basics-of-foreign-keys-in-mysql — sujeet, Jul 01 '19 at 10:48
Possible duplicate of [Delete from two tables with join?](https://stackoverflow.com/questions/4934418/delete-from-two-tables-with-join) — Qirel, Jul 01 '19 at 10:48
Also using `exit();`, `die();` or `__halt_compiler()` is pretty much mandatory for security after using `header("location:companylist.php");` as you should not be trusting the http client to be actually following the redirect and see something what not should be possible and also to prevent unwanted possible code to be executed after.. — Raymond Nijland, Jul 01 '19 at 11:14
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Jul 01 '19 at 12:22
Yes, I'm aware of SQL injection, this is only for quick testing, just a little project using false data. — jpcode, Jul 01 '19 at 12:50
There's no reason not to get into the habit of doing it properly every time. It's not very difficult. It can also prevent some silly syntax errors as well. Plus if you do decide to re-use the code in another "real" project you don't have to re-write and re-test the database code — ADyson, Jul 01 '19 at 23:21

score 0 · Answer 1 · answered Jul 01 '19 at 14:53

To create the required table structure, I used these create statements:

CREATE TABLE company (
idcompany INT,
company_name VARCHAR(31),
PRIMARY KEY (idcompany));

CREATE TABLE clients (
idclient INT,
idcompany INT,
PRIMARY KEY (idclient),
FOREIGN KEY (idcompany) REFERENCES company(idcompany) ON DELETE CASCADE);

CREATE TABLE queries (
idclient INT,
FOREIGN KEY (idclient) REFERENCES clients(idclient) ON DELETE CASCADE);

Then, to delete all references to a company, you can simply run

DELETE FROM company WHERE idcompany = (insert company id variable here);

You can view a working sql demo here.

If you need to change the constrain to contain DELETE CASCADE then look at this post.

Delete Query constraint fail

1 Answers1