php - 删除查询约束失败

Question

我正在为我的数据库创建一个添加/更新/删除系统，在编写删除查询代码时，我发现约束失败，阻止我从特定行中删除某些数据。

删除查询:

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);
    }
}

所需表格:

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

错误:

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

基本上，我通过 URL 获取行号，并尝试删除所有三个表上与该 idcompany 相关的所有数据。有人知道如何在删除查询中关联所有三个表吗？

Answer 1

为了创建所需的表结构，我使用了这些创建语句:

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);

然后，要删除对公司的所有引用，您只需运行

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

您可以查看有效的 sql 演示 here .

如果您需要更改约束以包含DELETE CASCADE，请查看 this post .