LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 09-30-2008, 09:36 PM   #1
ta0kira
Senior Member
 
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Rep: Reputation: Disabled
AUTO_INCREMENT + alphanumeric in MySQL?


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.
 
Old 10-01-2008, 01:02 AM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
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?
 
Old 10-01-2008, 01:47 AM   #3
ta0kira
Senior Member
 
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
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

Last edited by ta0kira; 10-01-2008 at 01:51 AM.
 
Old 10-01-2008, 02:18 AM   #4
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
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.
 
Old 10-01-2008, 07:26 PM   #5
ta0kira
Senior Member
 
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
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
 
Old 10-01-2008, 07:37 PM   #6
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
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?
 
Old 10-01-2008, 07:50 PM   #7
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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:

Code:
SELECT h.name, h.details FROM hardware h INNER JOIN user u ON u.id = h.userid WHERE u.fname = 'John' AND u.lname = 'Doe'
The SQL can be simplified if you capture the user ID in code but I wanted to show the concept.
 
Old 10-01-2008, 10:26 PM   #8
ta0kira
Senior Member
 
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
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
 
Old 10-02-2008, 07:56 PM   #9
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,235

Rep: Reputation: 545Reputation: 545Reputation: 545Reputation: 545Reputation: 545Reputation: 545
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
 
Old 10-02-2008, 10:51 PM   #10
ta0kira
Senior Member
 
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
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
 
Old 10-02-2008, 11:30 PM   #11
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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.
 
Old 10-03-2008, 10:15 AM   #12
sundialsvcs
Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 5,452

Rep: Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172
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.
 
Old 10-03-2008, 01:46 PM   #13
ta0kira
Senior Member
 
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
Good point. Thanks.
ta0kira
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
arithmetic on alphanumeric in PERL gaynut Programming 2 09-08-2008 10:09 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. SpellChainz Linux - Newbie 1 06-23-2007 04:35 PM
C fstat, st_uid and alphanumeric userID banan Programming 2 10-08-2006 07:17 PM
Mysql auto_increment problem. wood Programming 1 02-25-2004 11:40 AM


All times are GMT -5. The time now is 03:53 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration