string or int as primary key?

[ganjasensation0098]

Which is the better data type in creating primary key of a table?

[mandy0]

It depends on usage. But most of the times INT key is preferred. usually you will find something like ID to show the primary key.
Well it depends on the usage .. what kind of requirements you are going to have.. accordingly decide. But INT is most preferred.

[lemon-tree]

I can't think of any situation in which a string primary key would be necessary, as the aim of the key is to provide a unique way of finding and linking rows across tables; whilst a string primary key would work, an auto-imcrement int key will not only be more efficient, but you'll also find that it is faster to search. Having a string primary key will mean all searches have to go through as a string, whilst with a integer primary key, the ones that don't need the string will benefit from not having to use it.
Of course, if you need to have a key on a string, have it as a unique index alongside your primary key rather than using it as your actual primary key.

[mandy0]

@lemon-tree
you can have EMAIL as Primary key also ;) As well as it's possible you might go for Unique key ;) :D nvm ) but very very rearly string would be used ^_^

[lemon-tree]

you can have EMAIL as Primary key also

I certainly wouldn't, I would create a table like this instead:

USER_ID INT() - PRIMARY KEY
EMAIL VARCHAR() - UNIQUE KEY
etc for other user info

This way, you can create more simple foreign keys between tables and cross-reference tables.
Of course, it's always down to the programmer's discretion as to which method they chose. All methods are correct, but some methods are more correct than others.

[misson]

Expanding on lemon-tree's answers: using a string for a primary key may impact both space and time performance. Numeric types are smaller, so indexes and foreign keys on numbers are smaller than those on string types. In particular, indexes and foreign keys will duplicate values; better to duplicate small values rather than larger. Smaller sizes reduces page-outs. Numeric comparison is faster than string comparison.

See also:

• "Surrogate vs. natural/business keys"
• "How do you like your primary keys?"
• "What's the best practice for primary keys in tables?
• "Use item specific prefixes and autonumber for primary keys?"
• "Should I have a dedicated primary key field?".