Using Foreign key with Cascade

Question

On my system, when deleting a post, all comments that have the id_publicacao same as the publication are deleted from a table called comentarios_publicacao. And I do it simply by doing a check like:

if(deletar a publicacao) {
    então eu apago todos os comentários com a id_publicacao igual ao da publicacao que foi apagada
}

So I was doing some Google searches when I came across the famous Foreign Key then I researched about. But I didn’t understand very well... I also saw something related to Cascade that if I erased something in the "father table", everything related to it in "daughter tables" would also be erased. Could I replace the method I use (as well as the "example" I gave above) with that of the daughter tables etc? And how would I do that? As this subject is new to me I’m still a little lost, I saw several explanations on blogs and sites about Foreign Key but I couldn’t really understand when to use it. Could someone give me a clear and direct explanation about the concept of Chave Estrangeira and derivatives thereof (type, what are constraints, Scade, etc...)?

Answer 1

It is through the use of Foreign keys(Foreign Key) that the Relational Databases ensure the Referential integrity (Referential Integrity).

Foreign keys serve to define relations between tables.
The relation is defined by the inclusion of one or more fields in the table daughter that represent the primary key of the table father. These fields are called foreign key.

This relationship is created a FOREIGN KEY Constraint(foreign key restriction).
It is called restriction (Constraint) because, after the relationship between the tables has been defined, the operations on the two tables are conditioned in order to guarantee the integrity of the data.

This guarantee means not allowing:

1 - include in the table daughter values that do not exist in the table father.
2 - exclude records from the table father that are referenced in the table daughter.

To facilitate deletion of records from the table father without violating the second restriction, FOREIGN KEY Constraint can be created with the option DELETE CASCADE.
This option informs the database so that when a record father is deleted, delete all records from the table daughter which have reference to it.

To FOREIGN KEY Constraint can be created in the CREATE TABLE

CREATE TABLE comentarios_publicacao
(
    Id int NOT NULL,
    .......,
    .......,
    Id_publicacao int,
    PRIMARY KEY (Id),
    FOREIGN KEY (Id_publicacao) REFERENCES Publicacao(Id) ON DELETE CASCADE
)

or in the ALTER TABLE

ALTER TABLE comentarios_publicacao
ADD FOREIGN KEY (Id_publicacao)
REFERENCES Publicacao(Id) ON DELETE CASCADE