Conflict DELETE Statement with reference to Foreign Key

Question

Conflict DELETE Statement with reference to Foreign Key

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

Use SQL Server 2017 with Management Studio and Java 8.

I have a database about movies and I receive an error when trying to delete a movie that is referring to another table that acts as an intermediate.

Diagram (example values in red):

Error:

SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK_PELICULAS_GENEROS_PELICULAS". The conflicted occurred in database "industriaCine", table "dbo.PELICULAS_GENEROS", column "movie_id".

Code to delete:

public boolean eliminarPelicula(int id) {
        sSQL =  "DELETE FROM peliculas WHERE id_pelicula=?";

        boolean resultado;

        // Java 7 try-with-resources
        try (PreparedStatement pstm = con.prepareStatement(sSQL)) {

            pstm.setInt(1, id);
            pstm.executeUpdate();

            resultado = true;

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "SQLException:\n" + e, "Error: eliminarPelicula()", JOptionPane.ERROR_MESSAGE);
            resultado = false;
            }

        return resultado;
    }

The Foreign Key I did it this way:

ALTER TABLE PELICULAS_GENEROS ADD CONSTRAINT FK_PELICULAS_GENEROS_PELICULAS FOREIGN KEY (id_pelicula) REFERENCES PELICULAS(id_pelicula);

The idea is that if I delete a id_pelicula , we will also delete id_pelicula from table PELICULAS_GENEROS . Example (as in the photo above), if I delete the movie with id_pelicula = 2 , the error appears.

First time I do something like this.

What's happening? Why the error? Should I change something in SQL Management Studio in references?.

I'm lost, thanks in advance for the help.

mysql sql java

asked by Robert Gomez 13.11.2017 в 04:11

source

2 answers

0

I'm not very familiar with SQLserver but I think the problem you have is that you try to delete a record from the FILM table knowing that the MOVIE id that is in the FILMS_GENERO table is still persisting.

The solution would be to first remove the data from the PELICULA_GENERO table:

DELETE FROM PELICULA_GENERO WHERE ID_PELICULA= 2

Then properly delete the record from the table FILM:

DELETE FROM PELICULA WHERE ID_PELICULA =2

answered by 13.11.2017 в 05:09

Triangle in java error expected primary expression before

score 2 · Accepted Answer

The error occurs because there is a reference to a record (in this case, the movie of id 2) in another table, and you want to delete that record. By default, the database will not let you delete it. However, when creating the relationship, you can specify through the execution query that you want the database to "delete in cascade" when deleting a record. The same thing happens when you want to update a record.

ALTER TABLE peliculas_generos
drop CONSTRAINT [NOMBRE_CONSTRAINT de ID_PELICULA];

ALTER TABLE peliculas_generos
ADD CONSTRAINT [NOMBRE_CONSTRAINT de ID_PELICULA]
    FOREIGN KEY (id_pelicula)
    REFERENCES pelicua
        (id_pelicula)
    ON DELETE CASCADE;

Clarification: What is between [] (with [] inclusive) is information to be replaced.

Anyway, you have control in java and you can easily perform the cascade deletion manually, that is, you first delete the movie_genre table when the movie_id is = 2 and then delete it in the movie table. Sometimes with the simple creation of one more method you can avoid mistakes in the future.