Can I use VARCHAR as the PRIMARY KEY?

mysql sql primary-key unique varchar
Mike Brady picture Mike Brady · Oct 10, 2013 · Viewed 104.4k times · Source

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR.

My rationale is that, each coupon will have a unique code, and the only commands I will be running are SELECT ... FROM ... WHERE coupon_code='..'

I won't be doing any joins or indexing, and I don't see there ever being more than a few hundred entries in this table.

It seems to me that this will be OK, but I don't know if there is anything I'm missing/not thinking about.

Answer

Sergey Kalinichenko picture Sergey Kalinichenko · Oct 10, 2013

Of course you can, in the sense that your RDBMS will let you do it. The answer to a question of whether or not you should do it is different, though: in most situations, values that have a meaning outside your database system should not be chosen to be a primary key.

If you know that the value is unique in the system that you are modeling, it is appropriate to add a unique index or a unique constraint to your table. However, your primary key should generally be some "meaningless" value, such as an auto-incremented number or a GUID.

The rationale for this is simple: data entry errors and infrequent changes to things that appear non-changeable do happen. They become much harder to fix on values which are used as primary keys.

Related questions

MySQL unique column string

Currently I have this table in my database: CREATE TABLE `twMCUserDB` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mc_userName` text NOT NULL, `mc_userPass` text NOT NULL, `tw_userName` text NOT NULL, `tw_userPass` text NOT NULL, PRIMARY KEY (`id`) ) …

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More
difference between primary key and unique key

I'm using mysql database. I have a confusion between primary key and unique key. Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

Read More