So I know that many argue that char as primary key is a bad practice, but I need it in this project. So I creating an API that receive book id from http params. I only received a single id, but the data can come from 2 tables, one is publicBooks and another is privateBooks. I can do query to search the id in both, but I need a way to find the table to search. So I decide to use char to my privateBooks table so the id will look like:
p-1
p-2
p-3
...
So I can see if the http req has p- then i will look in privateBooks, else i will look at publicBooks. But I don't find a way to automatically set the id as p-i, with i as auto increment integer. Anyone know how to do this?
You don't have to change your database. Just select something along that tells you the table. This can be 'p' or 1/2 or even better the table name. So, if you get the parameters 1 and 'publicBooks', you know this is about the ID 1 in publicBooks.
Here is an example that looks for an author's books in both tables:
select id, 'publicBooks' as tbl from publicBooks where author_id = 123
union all
select id, 'privateBooks' as tbl from privateBooks where author_id = 123;