Re: Auto incrementing key

chanakam2000_at_gmail.com wrote:
> |BTW: It is very very bad form to ever create a key of the type
> 'S-00001'
> |you demonstrate above. Don't do it. If you need the "S" put it into a
> |separate column and leave out the hyphen
>
> I want to generate 'S-00001' (this was example) type key because to
> identify student from several department
>
> Ex: For engineering student en-00001,....
> For Agriculture student ag-00001,.... etc
>
> I have to have keys for book codes also
>
> Ex: For computer subjects book co-00001,....
>
> keeping seperate column for s and 00001 idea is Ok. (I will have to use
> two columns as primary key);
> But even I don't know how to get 00001,00002 from sequence
> i can get 1,2,3 (not 5 digit format) [I used trigger for inserting]
>
> I'm Sorry for my bad english. I hope you can understand my question.
>
> Please help me.
>

Your model is wrong; you need a table with types_of_study, containing Agriculture, A
Engineering, E
Computer Science, C
etc.

In your students table, you have a column type_of_study, pointing to the table types_of_study (mind the extra 's').

And there you have problem #1: what if a student finds out agriculture isn't his or her thing, and switches to Computer Science?!?
Is c-0001 the same student as a-0001? What if you already have a c-0001 student?.

Just give the students a unique number, meaningless. A sequence is ideal for that. Make that your PK, and search for another unique identifier.
Name, date and place of birth are combinations used, or social security number (if that exists) or some other, preferably official, identifying ID.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...