mysql triggers on delete a raw

I have two tables as students and certificates, I need to delete the certificates related to a particular student from the certificates table when deleting a student from the student table. I am not much familiar with mysql triggers. please assist me.

this is what I've tried but I not sure it is correct

DELIMITER $$  
CREATE TRIGGER delCerts AFTER DELETE ON students
FOR EACH ROW
BEGIN
DELETE FROM certificates as certs WHERE certs.stCode = students.stCode;
END;
$$

Answers


In InnoDB databases you could achieve it by having foreign key in certificates table:

idStudent INT NOT NULL,
FOREIGN KEY (idStudent) REFERENCES Students (id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

When you delete student from stuends table - all related certificates will be also deleted. I think it's better than trigger, because if you or someone will disable triggers (accidentally or on purpose) your db will be inconsistent.


Need Your Help

What is difference between method `toHaveBeenCalled()` and `andCalledThrough()`

angularjs unit-testing jasmine karma-jasmine

While using the Jasmine Spies, how different is andCalledThrough() method from toHaveBeenCalled, does it actually run the original method completely? Any ideal scenarios when I should use it?

Using Enthought Python instead of the system Python

python linux installation enthought

I've installed the Enthought Python Distribution, which is basically a glorified Python distribution with added libraries for numerical and scientific computing. Now, since I use Debian, there is P...