SQL, Relation between SQL tables

Asked

Viewed 37 times

2

My question is about the list of tables in . In an academy system I want to make a relationship between the student and his training record. I made a relationship from one to one, because each student can only have one record.

Also, I put the foreign key on the chart. My goal is that when I delete a student from the system his record will be deleted as well. However, when I try to delete a student this message appears:

{com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row:}

What is my mistake? the foreign key should be on the student table?

mysql sql
  • 1

    To delete a data from a table and automatically delete the data from another vc you can use Cascade

  • 1

    That was the problem, Big Brother!

  • I’m happy to help, if you can accept my answer, I thank you

1 answer

3


You can do it this way:

Creates the student table:

CREATE TABLE Aluno (
    idAluno INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    idade int not null
);

Create the table sheet:

CREATE TABLE Ficha(
    idFicha INT PRIMARY KEY AUTO_INCREMENT,
    exercicio VARCHAR(255) NOT NULL,
    idAluno INT NOT NULL,
    FOREIGN KEY (idAluno)
        REFERENCES Aluno (idAluno)
        ON DELETE CASCADE
);

Note that we have added to ON DELETE CASCADE clause at the end of the definition of foreign key restriction.

Then you insert data into both tables;

To delete you will only need to do this

DELETE FROM Aluno WHERE idAluno = 2;

OBS.: The fields of my tables I invented, because I do not know the fields you want, I did this way only to clarify the idea!

Source: http://www.mysqltutorial.org/mysql-on-delete-cascade/

I hope I’ve helped!

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.