Create an Autonum column with values 100-1, 100-2, 100-3

[gradinumcp]

Hi!

I need help creating a column in a table of a SQL database. The data should be something like 100-1, 100-2, 100-3... There will also be data like 101-1, 101-2..... Here 100 and 101 point to Branches and are significant like 100-California, 101-Idaho etc. And the 1,2,3... point to computer stations in each of the offices.

So as and when I add records to this table, it should update the Computer Station ID to the next one and give me the option to add the Branch ID before that.

Thanks!

 

[sleipnir214]

I don't believe you can get MySQL to generate a single-column value of that sort. There are, however, some things you can try with multicolum primary keys that include and auto_increment column. Take a look here:

http://dev.mysql.com/doc/mysql/en/example-auto-increment.html

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ericbrunson]

Autoincrement is evil.

It is non-standard and it actually ends up creating more problems than it solves. A well thought out programmatic design, object oriented or functional, far outweighs the temporary convenience (laziness) of auto increment.

Your scenario is actually a poster child example of why a programmatic solution would be a better approach. If you write a single set of functions/objects that you always use to manipulate the columns in question and build on those, I guarantee you'll be happier in the long run.

 

[r937]

If you write a single set of functions/objects that you always use to manipulate the columns in question and build on those, I guarantee you'll be happier in the long run.

this implies that i can no longer submit a query like

insert into tableA (name) values ('foo')

simply because the table has a surrogate key? i have to use a function/object for even the most simple of sql statements?


no thanks!!


auto_increment is actually harmless, provided that people don't misuse it

i agree, attempting to get numbers like 100-1, 100-2, etc., would be misusing it


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ericbrunson]

Rudy,

You know how much I respect your opinion, and in this simplest case you could make a good argument, but I'd rather take the extra time to type "insert values ( myseq.nextval, whatever ) into mytable". Too bad you can't in mysql.

Auto inc is great if you're using a completely pure PK value, which I always do (more lessons learned), but like you said, when people start putting intelligence in their keys, it's just an inevitable downward spiral.

I guess I was coming from the viewpoint of writing an application and pretty much relying on that application to manipulate your data. Once my base data is generated (programmatically, usually) I rarely manually insert records. Again, stored procedures would be the kick ass way to go, but too bad you can't use them in a production version of MySQL (yet).

Personally, I'd take views over stored procs, but you take what you can get.

 

[r937]

pretty much relying on the application to manipulate your data" is awfully narrow thinking

i have yet to see (and i have 25+ years of professional experience working with databases) an application in real life which actually managed to subjugate the data to its own code

data has a longer life expectancy that applications, and you should not cripple your database by making all access dependent on application code

just a rule of thumb that has so far proven very successful



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ericbrunson]

Okay, you've got about 6 years on me, I've only been doing database development for a little over 19 years, but I think that's long enough to be able to speak from experience and I'm willing to stand my ground. Granted, I've only been using MySQL for about 4 years, my previous experience was primarily Oracle with some Sybase, Informix and DB2 thrown in, my opinions and experience are colored by that.

I personally go in and manipulate data by various means: direct SQL, scripts, CSV loads, but other than the occasional DBA, everyone else accesses the data via application code. I don't think the tiny convenience of having an autonumber field outweighs the disadvantages of using a non-standard SQL extension. And just from personal experience in my short time with MySQL, about every other time I've given in to the temptation of using an autonumber field, something comes along that makes me regret the decision.

That being said, I have some extremely sophisticated objects that know how to read the database schema, including foreign keys and constraints, generate the SQL required to CRUD the records, generate HTML to interact with the user, validate the data and, trivially, handle the creation and manipulation of the primary keys. Since this code works on Oracle, Informix, Sybase, DB2, MSSql, Postgres and MySQL, having the non-standard autoincrement just gets in the way.

I will concede that if you are only ever going to run on MySQL, have no interest in adhering to standards and end up *having* to go in and add records via straight SQL rather than taking 5 minutes to create an interface so the actual application users can do it themselves, then sure, go ahead and use all the non-standard (though convenient) extensions of MySQL.

 

[r937]

you couldn't have been talking to me in that last paragraph, dude

by the way, how did you implement NEXTVAL in microsoft sql server?



as far as i know, the sql standard for sequences is still just a proposal

but i'm not a standards guru (ask joe celko if you really want a standards answer)

in any case, i understand your point, and i wish you good luck writing your applications

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[gradinumcp]

Thanks all you guys for your invaluable feedback--however I am still at loss as to what would be a better approach to tackle my problem. Some clear and simple language would be great for a begineer like me.

Thanks!

 

[r937]

simple language: take a look at the multi-column auto_increment information sleipnir214 posted

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ericbrunson]

by the way, how did you implement NEXTVAL in microsoft sql server?"

Actually, I don't even use sequences any more, even in Oracle, I just create a single row table called "sequence" and increment it with "select for update" whenever I need it.

Anyway, even though we may disagree on this, I alway read your posts closely because you teach me something new on a regular basis. It's your prompting that converted me over to LEFT JOIN syntax. My rational was: I don't have an argument against it and Rudy's a damn smart guy, so I'll go with it.

 

[r937]

thanks, eric

however, i'm nowhere near as smart as i used to be, and the older i get, the less i know...

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)