java - 如何解决 "Cannot add or update a child row: a foreign key constraint fails"

Question

我试图在不更改父表的情况下更新子表。确切地说，我有带有 id-album 、 name 等的父表 Album 。然后我有带有 id-song 的 Song 表，id-album 是外键引用表 Album ，然后是列名等等。例如，我在同一张专辑中有 2 首歌:歌曲 1 和歌曲 2 我想做的是从专辑中删除歌曲。

首先是有可能做到，如果可以的话，我想听听您的建议。我试图更新数据库中的那一行以及我之前没有提到的 javafx 中的 tableView。请记住，如果那是该专辑中的最后一首歌，我需要删除该专辑，因为条件是这样的专辑只有在其中至少有一首歌时才会存在。

如果您需要更多信息，我会上传一些代码、信息或类(class)。提前致谢。

这些是创建表的查询。

 private void createTableAlbum() throws SQLException {
    Statement statement = conect.createStatement();
    String query = "CREATE TABLE IF NOT EXISTS album("
            + "id_album INT NOT NULL AUTO_INCREMENT,"
            + "album_name VARCHAR(30) NOT NULL,"
            + "no_hours smallint NOT NULL,"
            + "publishing_year date NOT NULL,"
            + "PRIMARY KEY(id_album));";

    statement.execute(query);
}

private void createTableSong() throws SQLException {
    Statement statement = conect.createStatement();
    String query = "CREATE TABLE IF NOT EXISTS song("
            + "id_song INT NOT NULL AUTO_INCREMENT,"
            + "id_album INT,"
            + "song_name VARCHAR(30) NOT NULL,"
            + "song_time smallint NOT NULL,"
            + "number_on_album smallint,"
            + "PRIMARY KEY(id_song),"
            + "FOREIGN KEY (id_album) REFERENCES album(id_album) ON DELETE CASCADE ON UPDATE NO ACTION);";
    statement.execute(query);
}


public void updateSong(Song song, Album album) throws SQLException {
    conect = db.openConnection();
    PreparedStatement statement = conect.prepareStatement("UPDATE song"
            + " SET id_album = ?, song_name = ?, song_time = ?, number_on_album = ? WHERE id_song = ?;");
    statement.setInt(1, album.getAlbumId());
    statement.setString(2, song.getSongName());
    statement.setShort(3, song.getSongTime());
    statement.setShort(4, song.getNumberOnAlbum());
    statement.setInt(5, song.getSongId());
    statement.executeUpdate();
    db.closeConnection();
}

我已经尝试过类似的方法，但它所做的是从数据库中删除该特定歌曲，并且它正在处理我在下面记下的约束错误。错误行在这里

pesmicaDao.updatePesmica(newPesmica);

 Pesmica currentSong = tablePesmica.getSelectionModel().getSelectedItem();

            if (currentSong == null) {
                alert1.setContentText("You have to select row first");
                alert1.showAndWait();
            } else {

                Album album = new Album();
                album.setId_album(0);
                short serialNumberOnAlbum = 0;
                Pesmica newPesmica = new Pesmica(album, currentSong.getNaziv_pesme(), currentSong.getTrajanje(), serialNumberOnAlbum);
                newPesmica.setId_pesme(currentSong.getId_pesme());

                try {
                    pesmicaDao.updatePesmica(newPesmica);
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                int i = pesmica.indexOf(currentSong);
                pesmica.removeAll(currentSong);
                pesmica.add(i, newPesmica);
                tablePesmica.getSelectionModel().clearSelection();

错误代码如下:

 com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`projekatbaze`.`pesmica`, CONSTRAINT `pesmica_ibfk_1` FOREIGN KEY (`id_album`) REFERENCES `album` (`id_album`) ON DELETE CASCADE ON UPDATE NO ACTION)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
at Dao.classes.PesmicaDAO.updatePesmica(PesmicaDAO.java:49)
at Paneli.PregledPesamaPanel.lambda$start$6(PregledPesamaPanel.java:442)

解决方案:

我在这里找到了一些有用的建议，所以首先让我谢谢你，我很感激。那么现在我将用答案更新我的帖子，以便有类似问题的人可以发现这篇帖子有用。

public void updateSong(Song s) throws SQLException {
    conect = db.openConnection();
    PreparedStatement statement = conect.prepareStatement("UPDATE song"
            + " SET id_album = ?, naziv_pesme = ?, trajanje = ?, rb_na_albumu = ? WHERE id_song = ?;");
    if (s.getAlbum().getId_album() == 0) {
        statement = conect.prepareStatement("UPDATE song"
                + " SET id_album = null, naziv_pesme = ?, trajanje = ?, rb_na_albumu = null WHERE id_song = ?;");
        statement.setString(1, s.getNaziv_pesme());
        statement.setShort(2, s.getTrajanje());
        statement.setInt(3, s.getId_song());
    } else {
        statement.setInt(1, s.getAlbum().getId_album());
        statement.setString(2, s.getNaziv_pesme());
        statement.setShort(3, s.getTrajanje());
        statement.setShort(4, s.getRb_na_albumu());
        statement.setInt(5, s.getId_song());
    }
    statement.executeUpdate();
    db.closeConnection();
}

Answer 1

我认为我找到了解决方案，但我没有完整的源代码，所以我不确定是否是这个导致了你的问题。

问题是您在调用 album.setId_album(0);

时将 id_album 设置为 0

然后，当您尝试更新行时，您没有任何 id 为 0 的相册，这会导致问题，而当使用 0 更新时，您可以检查该值是否为 0 并将准备好的语句中的 id_album 设置为 null(因为 int 可以'在 java 中为空)，像这样:

 if (p.getAlbum().getId_album() == 0) {
  // set id_album value inside statement to null
    statement = conect.prepareStatement("UPDATE song"
        + " SET id_album = null, song_name = ?, song_time = ?, number_on_album = ? WHERE id_song = ?;");
}

编辑:
我忘了说你仍然需要相应地设置其他值，也可能有更简洁的方法来做到这一点，但只是试着看看这是否是导致问题的原因。