0

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 the record from a 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.

$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

kvantour
  • 25,269
  • 4
  • 47
  • 72
Mr_question
  • 71
  • 2
  • 10
  • Possible duplicate of [Mysql: delete rows in two tables with foreign keys](https://stackoverflow.com/questions/39768995/mysql-delete-rows-in-two-tables-with-foreign-keys) – William Perron Jun 07 '18 at 12:40
  • which foreign key is being violated? – ADyson Jun 07 '18 at 13:35
  • I am having this error Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`projectmgmt_osman`.`progress`, CONSTRAINT `progress_ibfk_1` FOREIGN KEY (`RegNo`) REFERENCES `students` (`RegNo`))' in C:\xampp\htdocs\projectmanager\adminhome.php – Mr_question Jun 07 '18 at 13:45

1 Answers1

1

It could be easier to split deletes into separate queries.

 DELETE FROM `progress` 
        WHERE `RegNo` IN(
          SELECT `RegNo` FROM `students` WHERE ProjectID = '$id'
        ); 

 DELETE FROM `student` WHERE `ProjectID` = '$id';
 DELETE FROM `projects` WHERE `ProjectID` = '$id';
Jirka Kopřiva
  • 2,939
  • 25
  • 28