如何使用PHP pdo在mysql中使用外键约束从数据库中删除数据 [英] How can I delete data from database with foreign key constraint in mysql using PHP pdo

问题描述

我在数据库中有三个表。



表1



项目



PK ProjectID



表2



学生



PK RegNo FK ProjectID表3



进度FK RegNo



现在，当我从项目中删除记录时我想要执行删除操作它应该从学生中删除，因为学生主键也作为外键进度表出现，所以它也应该从进度表中删除RegNo 。我如何才能以最佳方式实现这一目标。在此先感谢。



我尝试过：

I have three Tables in database.

table 1

projects

PK ProjectID

table 2

students

PK RegNo FK ProjectID table 3

progress FK RegNo

Now the thing i want to perform a delete operation when i delete record from project it should be deleted from students, as students primary key is also present as foreign key progress table, so it should also delete RegNo from progress table. How i can achieve this as the best possible way. Thanks in advance.

What I have tried:

$query = "

        DELETE students, progress from students inner join progress on progress.RegNo=students.RegNo where students.ProjectID='$id';
        DELETE FROM projects where projects.ProjectID='$id'; 
";

//$conn->exec($query);
$stmt = $conn->prepare($query); $stmt->execute();

它给出了外键约束违反

it gives foreign key constraint violation

推荐答案

query =

删除学生，从学生进步内部联接进度进展.RegNo = students.RegNo其中students.ProjectID ='

query = " DELETE students, progress from students inner join progress on progress.RegNo=students.RegNo where students.ProjectID='

id';
DELETE FROM项目，其中projects.ProjectID ='

id'; DELETE FROM projects where projects.ProjectID='

id';
;

//

id'; "; //

这篇关于如何使用PHP pdo在mysql中使用外键约束从数据库中删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！