sql - VARCHAR as foreign key/primary key in database good or bad?

Question

Is it better if I use ID nr:s instead of VARCHARS as foreign keys? And is it better to use ID nr:s isntead of VARCHARS as Primary Keys? By ID nr I mean INT!

This is what I have now:

category table:
cat_id ( INT ) (PK)
cat_name (VARCHAR)

category options table:
option_id ( INT ) (PK)
car_id ( INT ) (FK)
option_name ( VARCHAR ) 

I COULD HAVE THIS I THINK:

category table:
cat_name (VARCHAR) (PK)

category options table:
cat_name ( VARCHAR ) (FK)
option_name ( VARCHAR ) ( PK )

Or am I thinking completely wrong here?

Answer 1

The problem with VARCHAR being used for any KEY is that they can hold WHITE SPACE. White space consists of ANY non-screen-readable character, like spaces tabs, carriage returns etc. Using a VARCHAR as a key can make your life difficult when you start to hunt down why tables aren't returning records with extra spaces at the end of their keys.

Sure, you CAN use VARCHAR, but you do have to be very careful with the input and output. They also take up more space and are likely slower when doing a Queries.

Integer types have a small list of 10 characters that are valid, 0,1,2,3,4,5,6,7,8,9. They are a much better solution to use as keys.

You could always use an integer-based key and use VARCHAR as a UNIQUE value if you wanted to have the advantages of faster lookups.