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.
This question is related to
mysql
sql
primary-key
unique
varchar
A blanket "no you shouldn't" is terrible advice. This is perfectly reasonable in many situations depending on your use case, workload, data entropy, hardware, etc.. What you shouldn't do is make assumptions.
It should be noted that you can specify a prefix which will limit MySQL's indexing, thereby giving you some help in narrowing down the results before scanning the rest. This may, however, become less useful over time as your prefix "fills up" and becomes less unique.
It's very simple to do, e.g.:
CREATE TABLE IF NOT EXISTS `foo` (
`id` varchar(128),
PRIMARY KEY (`id`(4))
)
Also note that the prefix (4)
appears after the column quotes. Where the 4
means that it should use the first 4 characters of the 128 possible characters that can exist as the id
.
Lastly, you should read how index prefixes work and their limitations before using them: https://dev.mysql.com/doc/refman/8.0/en/create-index.html
It depends on the specific use case.
If your table is static and only has a short list of values (and there is just a small chance that this would change during a lifetime of DB), I would recommend this construction:
CREATE TABLE Foo
(
FooCode VARCHAR(16), -- short code or shortcut, but with some meaning.
Name NVARCHAR(128), -- full name of entity, can be used as fallback in case when your localization for some language doesn't exist
LocalizationCode AS ('Foo.' + FooCode) -- This could be a code for your localization table...
)
Of course, when your table is not static at all, using INT as primary key is the best solution.
It is ok for sure. With just few hundred of entries, it will be fast.
You can add an unique id as as primary key (int autoincrement) ans set your coupon_code as unique. So if you need to do request in other tables it's better to use int than varchar
Source: Stackoverflow.com