Java SQL 插入，其中不存在 2 个相同的值

Question

在我的java应用程序中，我试图将“玩家回合”插入到我的数据库中，其中同一玩家不能再次处于相同的“holeNumber”。

我的玩家圆 table session

目前，我有这个，最后一行只是伪代码来显示我想要做什么。

"insert into PlayerRound(playerID, roundID, holeNumber, holeScore) "
    + "select (select playerID from Player where playerID = ?),"
    + "(select roundID from Round where roundID = ?),"
    + "(select holeID from HoleDetails where holeID = ?),? "
    + "WHERE playerID and holeNumber dont already exist in the table"; //correction in sql?

选择只是检查表中的外键。

我不明白该怎么做的部分是最后一行。我不知道“WHERE”语句应该放在哪里，也不知道它应该如何检查当前表中是否已经具有与先前插入的相同的holeNumber相同的playerID。

当前插入语句:

"insert into PlayerRound(playerID, roundID, holeNumber, holeScore) "
    + "select (select playerID from Player where playerID = ?),"
    + "(select roundID from Round where roundID = ?),"
    + "(select holeID from HoleDetails where holeID = ?), ?"

try {
        PreparedStatement pdt = conn.prepareStatement(sql1);
        pdt.setString(1, playerID);
        pdt.setString(2, roundID);
        pdt.setString(3, holeNumber.getText());
        pdt.setString(4, holeScore.getText());



        // put the rest of the code
        int n1 = pdt.executeUpdate();
        if (n1 > 0) {
            JOptionPane.showMessageDialog(null, "Inserted into PlayerRound Successfully!");

        }
    } catch (SQLException ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(null, "Insert into PlayerRound failed!");
    }

Answer 1

CREATE UNIQUE INDEX ix_player_round_hole
  ON PlayerRound (playerID, roundID, holeNumber);

将告诉数据库确保给定的玩家在给定的回合中只能访问给定的球洞一次，但可以在给定的回合中访问多个(不同的)球洞并在不同的回合中访问相同的球洞。然后您可以将查询简化为

INSERT INTO PlayerRound (playerID, roundID, holeNumber, holeScore)
  VALUES (?, ?, ?, ?)

如果您尝试违反此约束或链接到其他表的 FK 约束，则 JDBC 将引发可捕获的异常。