Has the ID column configured as PK, NN and AI, be the first column on my table definition? I have a .csv file with a bunch of data which I am using for load all the data into a DB table. The file lacks the ID column (because that's is an internal column managed through our software and they don't care about it).

To avoid issues with the data my "solution" was to move the column id to a position where doesn't interfere with the data from the file and that's right after ActiveFlag column or just at the end.

Check the following image for a graphical representation of the table:

Currently the id is my first column and I haven't any problem but I am trying to move to the end because the issue I am having with the data but I got the following error:

Operation failed: 
Executing:
ALTER TABLE `sedb`.`volume_pricing_agreement` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT AFTER `ActiveFlag`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`AgreementNumber`,`CustomerSiteID`, `CFProgramLevelID`, `Source`, `AgreementTypeID`, `id`);

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key


I don't know if the column has to be the first one on the table definition and if there is anyway to let it be wherever I want to or the solution maybe would be import the data by skipping the id column (supposing it's the first one).

I was checking the syntax here but I don't have a clue in how to skip a column during load:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]


This is the output of the command SHOW CREATE TABLE sedb.volume_pricing_agreement;:

volume_pricing_agreement, CREATE TABLE `volume_pricing_agreement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `AgreementNumber` int(11) DEFAULT NULL,
  `AgreementName` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
  `CustomerSiteID` int(11) NOT NULL,
  `CFProgramLevelID` int(11) DEFAULT NULL,
  `Discount` decimal(5,4) NOT NULL DEFAULT '0.0000',
  `Source` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `ActiveFlag` int(1) NOT NULL,
  `AgreementTypeID` int(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `AgreementNumber` (`AgreementNumber`),
  KEY `CustomerSiteID` (`CustomerSiteID`),
  KEY `ActiveFlag` (`ActiveFlag`)
) ENGINE=InnoDB AUTO_INCREMENT=2763 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


Any help? How should I deal with this?
No.  The order of columns in a table definition does not matter, with one old exception.  In older versions the "first" TIMESTAMP column had a special default DEFAULT value.

It is not required to have an AUTO_INCREMENT.  However it is (essentially) required that you have a PRIMARY KEY.  It smells like AgreementNumber would be such?  Keep in mind that a PK must be UNIQUE (a MySQL requirement).

As a side note, an A_I does not have to be the PK.  The only requirement is that it be the first column in some index.  And remember that a PK is a unique key is a key.

But, that is not your 'real' problem.  That is, you need to do LOAD DATA without the id being provided.

Plan A:  Load into another table, then copy it over.  When copying the data over, specify all the columns except the id; A_I will take care of the rest.

Plan B:  Use LOAD DATA, but specify all the columns except id.  Again A_I will take care of defaulting it correctly.

Other tips, while I have you on the line:


int(1) is a 4-byte number; the (1) means nothing.  Perhaps you want a 1-byte TINYINT?
Use INT UNSIGNED where appropriate
Use datatypes smaller than INT where appropriate; look up MEDIUMINT, and again make it MEDIUMINT UNSIGNED if appropriate.
Use NOT NULL when appropriate.
Don't index flags; they won't be used.
Learn about "composite" indexes. 


More

If you choose to have a 6-column UNIQUE key that includes some VARCHARs, having an A_I is probably better than making that the PK.

A UNIQUE KEY is two things:  An index, and a uniqueness constraint.  You can use it for either or both.

A PK is a UNIQUE KEY that identifies the row.  It also is "clustered" with the data, making lookups more efficient than with a "secondary key" (any non-PK).

When the 'cardinality' of an indexed column is very low, such as for a flag, the Optimizer will dynamically look at the value you provide and see if less than 20% of the table has that value.  In this case, it will use the index; else it will not.  The "20%" varies with the phase of the moon.  The reason for eschewing the index is that using the index involves bouncing back and forth between the index's BTree and the data's BTree via the PK.  This can be slower than simply scanning the data, tossing 80% of the rows.  A secondary key is a BTree containing the indexed column(s) plus a copy of the PK.