“没有主键” PostgreSQL 中 INNER JOIN 查询出错
问题是，两个表都有主键，而且到目前为止我对任何其他标准 SELECTS/INSERTS 都没有遇到任何问题。我是 JOINS 新手，但我不明白为什么这不起作用。
我收到此错误消息：
org.postgresql.util.PSQLException: No primary key found for table devicetable.
但是，两个表的主键都是我选择的列：deviceid / username。或许这也有关系吧？
我通过 JDBC
PreparedStatement query = curSQL.getConn().prepareStatement("SELECT devicetable.pushid FROM devicetable, usertable WHERE usertable.username=? AND usertable.deviceid = devicetable.deviceid", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
query.setString(1, username);
ResultSet rs = query.executeQuery();
if (rs.next()){
rs.updateString("pushid", pushID);
rs.updateRow();
}
rs.close();
query.close();
使用 SQL 访问它：
SELECT devicetable.pushid FROM devicetable, usertable
WHERE usertable.username=?
AND usertable.deviceID = devicetable.deviceID
The thing is, both tables do have primary keys, and I haven't had any problems so far with any other standard SELECTS/INSERTS at all. I'm new to JOINS, but I can't see why this isn't working.
I'm get this error message:
org.postgresql.util.PSQLException: No primary key found for table devicetable.
However, the primary keys for both tables are the columns I'm selecting for: deviceid / username. Perhaps this has something to do with it?
I'm accessing it via JDBC
PreparedStatement query = curSQL.getConn().prepareStatement("SELECT devicetable.pushid FROM devicetable, usertable WHERE usertable.username=? AND usertable.deviceid = devicetable.deviceid", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
query.setString(1, username);
ResultSet rs = query.executeQuery();
if (rs.next()){
rs.updateString("pushid", pushID);
rs.updateRow();
}
rs.close();
query.close();
With the SQL:
SELECT devicetable.pushid FROM devicetable, usertable
WHERE usertable.username=?
AND usertable.deviceID = devicetable.deviceID
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尝试显式连接：
Try an explicit join:
SELECT 中的 JOIN 子句与主键无关。您不需要为任何 SELECT 查询定义主键。
副作用可能是您得到重复的行，但这完全是另一回事。
实际上，您的查询中没有显式 JOIN。您可以使用显式 JOIN 重写查询，如下所示：
阅读有关 手册中的 JOIN 语法。
The JOIN clause in a SELECT has nothing to do whatsoever with primary keys. You don't need to have a primary key defined for any SELECT query.
A side effect may be that you get duplicate rows, but that is a different matter entirely.
Actually, there is no explicit JOIN in your query. You could rewrite your query with an explicit JOIN like this equivalent:
Read more about the JOIN syntax in the manual.