I'm very new to MySQL, but I have to come up with a new DB system for work. I'm picking it up pretty easily, but there's one thing I'd like to do that I don't know is possible. I'd like to use AUTO_INCREMENT primary keys, but I'll have 10 or so tables, so I'd like each of their primary key values to start with an alpha code specific to the table, e.g. "HW0001" for a hardware table and "US0001" for a user table, to avoid erroneous foreign key usage. I did think about starting each table at 1000000, 2000000, etc, but I don't know if I want numbers that high and it is still possible to run out. I saw a few web search results supposedly with an answer to the same question, but all required registration. Thanks.
ta0kira

PS I put this in the programming forum since technically SQL is a programming language.

AFAIK, auto-incr is only possible with purely integer cols. OTOH, if you have v5, you could use a DB trigger to generate the nums.
Erroneous (duplicate values) FKs should not be an issue, as you have to specify the table name when declaring/using them anyway.
Another option is to consider whether you really need this, or whether another 'natural' Primary Key (unique index) is avail from the data columns. I take you know that a PK can be a compound key?

Thanks. By "erroneous" I don't mean system-caused; I mean e.g. if table1 and table2 both have a row with primary key 0001, I don't want someone entering 0001 in a column meant for table1 foreign keys thinking that they're referencing 0001 from table2. If table1 primary keys start with T1 and table2 with T2, it's obvious that T20001 doesn't belong in a table1-designated column. This also prevents quick-glance errors when transposing viewed data, e.g. writing "0001" on the paper then forgetting whether it's a table1 or table2 primary key.

I considered using a trigger, but how would I keep track of the last value? The only way I can think of is to select the highest value present and add one, but I don't like that idea.
ta0kira

I understood what you meant, but my point is that auto-incr PKs are auto-generated, so you'd only specify them as part of the WHERE clause in SQL. If you or your users don't know what table they want, you've got much bigger problems
This is assuming you're allowing raw cmd line access to the DB. (not recommended)

Generally non-tech users have a screen form to fill out and don't need to know table names (or databases generally), the form handles that for them.
For programmers, you almost never specify absolute values for this stuff unless you are debugging the data.

Yeah, I'll probably restrict most access to a PHP interface. Should I infer that matching should be done with a search, e.g. if a new hardware row requires a reference to a user row, the person making the entry should have a search front-end with fields like user name, user ID, etc., then use their resulting choice to insert the primary key into the hardware row? I suppose they do have to come up with the row somehow, so it would be easier to never provide the primary key to the user. Thanks.
ta0kira

Sounds good. Try not to let users specify PK values, unless its a search and they can use values they understand eg username. You can use a drop down list if needed to let them choose the exact HW item they're after.
Then when you do the update, you'll already have the PK value in the array (hidden), won't you?

Generally the user never sees the primary key. I don't know the requirements of your application, but there are several ways in which you can approach it.

Here is one example:

Provide a screen where the user searches for the user name (which could be done by selecting one form a list provided, enter the name in a text box and search for a match, etc.) once a user is found then display a list of hardware associated with that user. Select the hardware for more details, including if you want the name of other users with similar hardware.

At no point does the user need to worry about awkward numbers just meaningful names.

Your SQL would be something like:

The SQL can be simplified if you capture the user ID in code but I wanted to show the concept.

Thanks. That's sort of what I had in mind, but this would be for creating new hardware records or updating them with a new user. I get the idea, though.
ta0kira

You can mimick auto-increment values by using ON DUPLICATE.

You'd define a column as your primary key, not autoincrement. Retrieve a record which has the highest value in this column using the MAX function. Insert your new record, purposely with this same key, but with ON DUPLICATE SET key_col = key_col+1.

Not sure what happens if another user has already inserted a record with the value of key_col + 1. The insert function might fail repeatedly, and keep incrementing key_col. If this is not the case, you could insert the new record and retrieving the max value in one SQL statement using a subquery. If this is not atomic yet, you might want to lock the table during execution of the query.

Maybe you can even use a key column with alpha characters, and then use string functions to split and concatenate the number part.

jlinkels

I thought of similar things, but I didn't want to require a linear search of the database just to come up with the next key. Thanks.
ta0kira

Well you could just use the aggregate function MAX, such as: id = MAX(id)+1

Or, do as the database does and set up a separate table to record the next id for your table and then append the string value of your choice

However, that then means that you key will need to be a string and thus require more storage. Part of the reason for Primary keys to be integer is that the unsigned int is a fairly efficient storage mechanism.

I find that application-code, not autoincrement features, is the best way to handle this case.

"autoincrement" is good for primary-keys in tables that would not otherwise have one. But they are not good for "numbers that are visible to the 'real world.' "

If you need to "coin" incrementing numbers, then write application code to do it and have the counters in a table. Use transactions, or locks.

The reason is this: eventually, something's gonna change about those keys. Not something in the program... something in the real-world of the business in question. And your program is going to have to adapt to that, quickly.

"The format of a human-visible identification number" is a business rule, and therefore it needs to be implemented in software.

Good point. Thanks.
ta0kira