Lobo Lobo - 5 months ago 14
MySQL Question

mySQL AUTO_INCREMENT column creation

I'm trying to make simple database since yesterday with primary id key with AUTO_INCREMENT option but keep getting this error:

Executing:
CREATE TABLE `spring`.`samochod` (
`idsamochod` INT NOT NULL DEFAULT AUTO_INCREMENT,
PRIMARY KEY (`idsamochod`));

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT,
PRIMARY KEY (`idsamochod`))' at line 2
SQL Statement:
CREATE TABLE `spring`.`samochod` (
`idsamochod` INT NOT NULL DEFAULT AUTO_INCREMENT,
PRIMARY KEY (`idsamochod`))

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT,
PRIMARY KEY (`idsamochod`))' at line 2


SQL Statement:

CREATE TABLE `spring`.`samochod` (
`idsamochod` INT NOT NULL DEFAULT AUTO_INCREMENT,
PRIMARY KEY (`idsamochod`))


I am getting this error even for normal integer columns. I have read many articles before and as far as I understand it should work this way without any problems.

Could anyone tell me what I'm doing wrong?

Answer

The problem is not the AUTO_INCREMENT but the DEFAULT before it. No value has been specified for DEFAULT. Just remove the DEFAULT keyword which is not needed here.

See also the CREATE TABLE syntax in the MySQL documentation, specifically:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]