CODEWITHSUNDEEP
postgresqljoinjdbcprimary-key
user705142
user705142

Reputation: 461

'No Primary Key' error with INNER JOIN query in PostgreSQL

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

Upvotes: 2

Views: 675

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

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:

SELECT devicetable.pushid
FROM   devicetable
JOIN   usertable USING (deviceID)
WHERE  usertable.username=?

Read more about the JOIN syntax in the manual.

Upvotes: 1

mu is too short
mu is too short

Reputation: 434685

Try an explicit join:

SELECT devicetable.pushid
FROM devicetable
JOIN usertable ON usertable.deviceID = devicetable.deviceID
WHERE usertable.username = ?

Upvotes: 2

Related Questions