if the categoryCode is the unique id of the table, do i need a numeric id too?

[searain]

Hi, for example, I have a table called Category.

The unique ID for Category is CategorCode which is given by the database
user when he created a new Category record. Such as SPORT_100

I usually also let the database auto created an auto increased ID for each
record, such as 10022321321 etc.

In my SQL statements, should I use the CategoryCode or the auto created
number ID to identify the record in updating, deleting, and relationship
link between tables?

Which is the better practice? The reason that I created this auto increased
ID in addition to the CategoryCode is that I am using this auto increased
number ID in the SQL statments instead of using the CategoryCode (which
input by database user later on.) So I don't have to worry about if the
Category Code is with special character to be considered in the SQL, such as
if the CategoryCode's value is SPORT'S_FUN_001 (with single quote).

Thanks

[Chris-the_dude]

The issue here is whether or not the Category Codes are enforced as unique values. If they are, you don't really even need the ID. All you need is one unique value per record to identify that record.

Anyway, if the category codes are unique, it doesn't really matter which value you use to identify your records, and you don't really need the ID.

If the category codes aren't unique, then use the ID to identify the values.

[ahundiak]

I like to use the auto incremented id's for links. That way, you only need one data type for your foriegn keys. It's just easier than having to remember that this key is a char(20) and that key is a char(10).