Delete rows from a table after a delete row from another table
EDIT. The original answer was wrong. Here's a new answer:
To remove lines of articles, you can use a trigger. This will be done before deleting the foreign key constraint cascade due to the BEFORE DELETING subheading.
CREATE TRIGGER before_delete_word_trigger
BEFORE DELETE ON word
FOR EACH ROW
BEGIN
DELETE FROM article
WHERE article.id_article IN
(SELECT DISTINCT id_article FROM R1 where R1.id_word = deleted.id_word)
END;
Old answer:
Other information for removing the cascade:
You can use the "ON DELETE" subclause in a foreign key constraint to invoke behavior on the relationship table.
CREATE TABLE R1
(
id_r1 serial NOT NULL PRIMARY KEY,
id_article int NOT NULL,
id_word int NOT NULL,
CONSTRAINT fk_r1_word
FOREIGN KEY (id_word)
REFERENCES words (id_word)
ON DELETE CASCADE,
CONSTRAINT fk_r1_article
FOREIGN KEY (id_article)
REFERENCES articles (id_article)
ON DELETE CASCADE
);
"ON DELETE CASCADE" allows your relationship table to be cleared after itself when something that indicates a delete. This way, your articles will always delete their words when the source of the word is deleted, and all data word relationships associated with that article are deleted if the article is deleted if necessary. None of the rows in the word or entry tables are affected if the link is removed.
There are other options to tune the automatic behavior when external strings are changed / removed. Here's some information on this for MySQL.
source to share