Let's say I have a table like this:
CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY );
Later on in my client, I may fetch a RESTful endpoint like this:
The query to retrieve the users would look like this:
SELECT * FROM user WHERE id = 1;
I want to avoid the approaches above so I can hide primary keys from user-facing web apps.
I thought I could do something like this instead to solve that problem:
CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, _id VARCHAR(20) NOT NULL UNIQUE );
The HTTP call now becomes something like this:
...and the query looks like this:
SELECT * FROM user WHERE _id = a129fjf24f0jwef94f;
What are the downsides to such an implementation at the database level, if any, other than excess storage? I would also have to use the
_id on queries where I involve joins of other tables, etc.