Why doesn't MySQL allow auto_increment on VARCHAR columns?

Question

I have the following SQL statement:

CREATE TABLE patient(
   patient_id VARCHAR(10) NOT NULL DEFAULT 1000 AUTO_INCREMENT PRIMARY KEY);

This statement does not work, and I do not understand why. Everywhere I see AUTO_INCREMENT only works with an integer. Why is it only for integers? What if I want a mix of INT and CHAR, and why doesn't the default value work?

Answer 1

this query does not work

The error message is telling you why. Simply put, you can't auto_increment character data. What would you even expect that to do, really?

Even if it was implemented (which I imagine it could be, but the MySQL team never had a compelling reason to do so), what would you do with un-printable and un-typable characters in your primary key? Imagine a record with the BELL character as the primary key. That doesn't sound like fun.

wherever I see auto_increment works with only int

Because only integers can meaningfully be incremented. They have a very well defined step for each increment and a very well defined boundary of values. Other data types don't.

what if I want it to be char and int mix up

Wanting something doesn't make it happen. Each column in a relational database has to have a defined type. One column can't have more than one type.

why default value does not work

Because you defined the column as character data, but defined the default as a numeric value. The default for character data has to be a character value.