CODEWITHSUNDEEP
mysqlselectindexingrowprimary-key
Jens Törnell
Jens Törnell

Reputation: 24768

How to uniquely identify all rows in a MySQL table without a primary key?

I use MySQL (in NodeJS). I have a table with unknown data.

I present the data as an HTML table by looping out the items. Then I add a click event to the cells. When that happens I want to load the data from the current cell.

How can I do that?

What I could do (but don't want to)

I could let the user add a custom_identifier to specify that in this table, use the id as identifier.

/*
From click event. I click on the cell with `column slug` with `id 3`.

cell = {
  identifier: 3,
  column: slug,
};
*/
const custom_identifier = "id";
const sql = `SELECT ${cell.column} FROM WHERE ${custom_identifier} = ${cell.identifier}`;

Because this approach requires the user to specify the identifier for each table, I don't want to use this approach.

Problem

When making SQL queries and wanting a particular row, we need a WHERE statement and an identifier to get the correct row. Some tables don't have an id or even a unique identifier at all.

Question

How can I get the data from a row where I don't know an id?

Ideas

Upvotes: 0

Views: 993

Answers (1)

Steve Chambers
Steve Chambers

Reputation: 39424

You could use this technique to add a row number to each row. Here's an example of it being used to generate an ID column with incrementing integer values starting from zero:

SELECT @idvar := @idvar + 1 AS id,
       tbl.*
FROM tbl, 
     (SELECT @idvar := -1) alias;

As the order of results from a SELECT isn't guaranteed, you may need to use this to create either a temporary table or a non-temporary table that is later deleted - e.g:

CREATE TABLE new_tbl AS 
SELECT @idvar := @idvar + 1 AS id,
       tbl.*
FROM tbl, 
     (SELECT @idvar := -1) alias;

Note: You'll get a "Duplicate column name 'id'" error if id is already the name of an existing column in the table. So unless you know this can't be the case it could be worth going with a more obscure column alias name, e.g. id_zyxwv or some other gobbledygook.

See working demo here: https://rextester.com/DVRGNM12570

Upvotes: 2

Related Questions