Creating IDs like AAA0000001

Question

Creating IDs like AAA0000001

Airolg Icsadjag

SSC Eights!

Points: 961
More actions
October 27, 2005 at 9:49 am

#169331

Hi!
I would like to create a "growing" ID like 'AAA0000001'.
(Growing ID means: first record in the table gets 'AAA0000001', second record gets 'AAA0000002', third record gets 'AAA0000003' and so on.)
How can i do this without using trigger or user defined function? Is there any solution in SQL server 2000 for this?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Nope!

You'll have to create the code and logic to do so your self. you'll either need to do it in a trigger, or handle all the logic in your insert procedures. What is the reason for having to have an identifier such as this?

homebrew01 SSC Guru Points: 55546 More actions · Answer 2

Do you really need 'AAA' and all those zeros ?? Why not just use an auto incrementing field ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

He wants to make an omelette without breaking eggs... can't wait to see that solution .

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 4

thanks for the quick answers!

So, I have to create an ID which has 3 alphabetic character and 7 number. Not only 'AAA' - it was just a sample. (It can be 'XYZ', too)

I have to do this, because several departments' users will create data into tables, and their records' IDs have to begin with the departments own 3 character (this is the 'AAA' in the sample).

You can say that it would be much easier to put the dept's ID into a single column and another column could be an identity column.

But! In this way there would be 2 column which can't use as a primary key if I would like to use it as a foreign key for another table.

So that is the problem. Can you suggest any brighter solution?

Ray M SSC-Insane Points: 21093 More actions · Answer 5

Sounds like a poor way to solve the problem.

Data integrity will be a major issue for this type of solution

What if a user creates a record for the wrong department?

You are violating the entire methodology of a RDBMS system.

You should have a department, with the 3 character code.

and you should have users that belong to departments, and the department/user id should be put on every row the user creates.

Department (departmentID int identity,

Name...

Abbreviation ...)

Users (UserID int identity

Fname ...

Lname ...

Username ...)

DepartmentUsers (UserDepartmentID int identity

UserID int

DepartmentID int)

ForeignKey UserID References Users.UserID

ForeignKey DepartmentID References Department.DepartmentID

then any table that creates records

Table1 (Table1id int

Whatever

UserDepartmentID) -- This will tell you what user/department wrote the record.

and the table id can be used as a FK anywhere.

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 6

Thanks very much, Ray!

I am going to think about it.

Ciao,

Gloria

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 7

Similiar to Ray's suggestion.

tableID (identity) as PK

then create a UNIQUE INDEX on the 2 other columns (department and number).

This will prevent the duplicates that you were trying to accomplish in your first post.

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 8

Yes, you're right! I choosed this way.

Thanks for you, too, SRB!

Bye, Gloria

[KH] SSCommitted Points: 1908 More actions · Answer 9

if your app is in control of creating record into the table you can also consider this

1. create a tabel to keep track of the ID with the following columns

- id_code varchar(30), -- PK

- id_prefix varchar(10),

- id_no integer default 0

- id_len integer

2. create a SP to get the ID

select @id = isnull(id_prefix, '') +

right('0000000000' + rtrim(convert(char(30), id_no)), id_len) +

isnull(id_sufix, '')

from id_table

where id_code = @id_code

update id_table

set id_no = id_no + 1

where id_code = @id_code

3. create a record in id_table for each of your ID.

insert into id_table (id_code, id_prefix, id_len) select 'ID_AAA', 'AAA', 7

insert into id_table (id_code, id_prefix, id_len) select 'ID_BBB', 'BBB', 7

I am using the above mtd for my apps. In-fact this has become my standard ID table for any type of ID and any len of ID in my Apps. The actual id_table that i have has much more column for diff purposes and the SP is much more complicated. But i guess you will get an idea from the above.

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 10

Im using a similar method for generate IDs, but it's not so elegant, that yours.

In the first row, I'd like to change this method to something else. (I dont want to run UDFs or SPs for generate.)

I got some ideas for this above.

Thanks for you, KH!

Gloria