Can I use VARCHAR as the PRIMARY KEY

Question

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

User · Answer

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    amp nbsp      Of course  when your table is not static at all  using INT as primary key is the best solution

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Can I use VARCHAR as the PRIMARY KEY?

Examples related to mysql

Examples related to sql

Examples related to primary-key

Examples related to unique

Examples related to varchar