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

Question

在我的java应用程序中，我试图在我的数据库中插入一个“玩家回合”，同一个玩家不可能第二次在同一个“holeNumber”中

我的玩具桌

目前，我有这个，最后一行只是伪代码，显示我正在尝试做什么

"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将抛出一个可捕获的异常