Well, varchar is probably a bad performance choice but as David says, a fixed char field would be less of a problem assuming it was a reasonable size. Integer fields are 4 bytes and anything larger of a fixed char field would be just that, larger. It is more expensive to compare strings generally but if the fixed char field were relatively small (like 4 to 8 bytes, guessing) then the performance hit should be minimal. Whether or not that performance loss is detrimental or even noticeable will really depend on your hardware, traffic and other elements of your app and DB design. Maybe you've got the horsepower to spare and maybe you don't.
That said, an integer PK is still your best performance choice.
just a small clarification
Suppose i have a table employee with following fields
ID EMP_ID FIRST_NAME LAST_NAME DESIGNATION
and i can expect EMP_ID to be in format like department + employee number ( example : IT_101 )
so would it be better to have only unique constraint on EMP_ID and going with the default PK?
Yeah, you want to index a secondary column and leave the PK as a key. Varchars as a PK is just bad design and slow.
If you want to use a :code field for record identification and routing paths overload param
[ruby] def to_param self.code end
Now you can call it via /resource_name/code
Thanks for the reply Naveen
CODE is of VARCHAR datatype, is there any other workaround for this?
What's the type of column code? As i know, only integer primary key supported in rails.