Tuesday, December 25, 2007

Assign Unique Numbers to Columns in Database - Oracle

The other day I had someone ask me how to automatically assign unique numbers (integers) to a column in an Oracle database. I took an Oracle SQL class a few years ago and I remember learning about the “create sequence” command. After reviewing a couple archived homework assignments for syntax, here are three typical examples of its use.

create sequence myblog_ID increment by 1 start with 100;
insert into MYBLOG (Blog, Author, ID) values (‘My SysAd Blog’,’esofthub’,myblog_ID.Nextval);

create sequence topblog_ID increment by 1 start with 1000;
insert into TOPBLOG (Blog, Author, ID) values (‘TopBlogLists’,’esofthub’,topblog_ID.Nextval);

create sequence mysysad_ID increment by 1 start with 10000;
insert into MYSYSAD (Blog, Author, ID) values (‘My SysAd Blog’,’esofthub’,mysysad_ID.Nextval);

1 comment:

Erek Dyskant said...

I ran into a very similar issue in MySQL the other day. The MySQL equivalent is:

SET @counter = -1;
UPDATE MyTable SET MyColumn=(@counter:=(@counter+1))

Note that you initialize @counter to -1 so that it ends up at zero for the first record, because the := operator returns the assigned value. If you want to 1 index instead of zero index, start @counter at 0.