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?
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.
