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

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

The answer is


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


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to primary-key

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object What is Hash and Range Primary Key? Can I use VARCHAR as the PRIMARY KEY? Can a foreign key refer to a primary key in the same table? UUID max character length MySQL duplicate entry error even though there is no duplicate entry Creating composite primary key in SQL Server What are the best practices for using a GUID as a primary key, specifically regarding performance? Add primary key to existing table Create view with primary key?

Examples related to unique

Count unique values with pandas per groups Find the unique values in a column and then sort them How can I check if the array of objects have duplicate property values? Firebase: how to generate a unique numeric ID for key? pandas unique values multiple columns Select unique values with 'select' function in 'dplyr' library Generate 'n' unique random numbers within a range SQL - select distinct only on one column Can I use VARCHAR as the PRIMARY KEY? Count unique values in a column in Excel

Examples related to varchar

SQL Server date format yyyymmdd What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? Difference between VARCHAR and TEXT in MySQL PostgreSQL: ERROR: operator does not exist: integer = character varying Can I use VARCHAR as the PRIMARY KEY? Is the LIKE operator case-sensitive with MSSQL Server? How to convert Varchar to Double in sql? SQL Server : error converting data type varchar to numeric What is the MySQL VARCHAR max size? SQL Server Convert Varchar to Datetime