[SOLVED] MySQL Key Column does not exist in Create Table

MySQL Key Column does not exist in Create Table

When I issue this command:

CREATE TABLE userxxx(
    idxxx INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (idxxx)
) Engine=InnoDB;

I get the error: Key column 'idxxx' doesn't exist in table. Should this statement not work?

I know I can add PRIMARY KEY right after AUTO_INCREMENT such as:

CREATE TABLE userxxx(
    idxxx INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) Engine=InnoDB;

then the command runs but this subsequent command to reference idxxx as a foreign key fails:

CREATE TABLE Requestxxx(
  RequestId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  idxxx INT NOT NULL,
  FOREIGN KEY (idxxx) REFERENCES userxxx (idxxx)
) Engine=InnoDB;

with the error: Failed to add the foreign key constraint. Missing column 'idxxx' for constraint 'requestxxx_ibfk_1' in the referenced table 'userxxx'

Solution

Mystery solved. In this code fragment

CREATE TABLE userxxx(
    idxxx INT NOT NULL AUTO_INCREMENT,

there were actually one or more invisible characters between the beginning of the line and the column name idxxx!! When I deleted all whitespace preceding idxxx and added back in 4 spaces the code worked. I had copied this create table statement from another source and when I view that source with notepad++ it does not show any weird chars. I do not know where they came from. Shouldn't MySql warn or detect non-viewable chars in a column name?? Rather ironic: MySql did not report any syntax errors in the statement.