MySQL error involving backticks

Question

I'm having some problems with this piece of mySQL code that is not wanting to get fixed

CREATE TABLE `DatabaseMGR`
  (
    `databaseID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `primCat` INT UNSIGNED NOT NULL, 
    `databaseName` VARCHAR(20),
     UNIQUE KEY (`databaseID`),
     PRIMARY KEY (`databaseID`),
     INDEX `databaseID`
)ENGINE = InnoDB;

It says that there is an error at line 1 with the regular "check your mysql syntax for right usage" error in response to ` usage. Is there something I'm missing? I'm new to sql so I might be missing something obvious.

Thanks.

Answer 1

The main point for your problem is at the line you are defining the index. In create table statement, you should use it with this syntax:

 create table table_name (
    ...
    index `INDEX_NAME` (`INDEX_COLUMN`)
 );

So you can fix your problem by changing your code to below:

CREATE TABLE `DatabaseMGR`
  (
    `databaseID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `primCat` INT UNSIGNED NOT NULL, 
    `databaseName` VARCHAR(20),
     UNIQUE KEY (`databaseID`),
     PRIMARY KEY (`databaseID`),
     INDEX `ix_databaseID` (`databaseID`) # Note the change on this line
)ENGINE = InnoDB;

However, in MySQL primary key column gets an index by default, so you can leave out that line totally, that results in the following code:

CREATE TABLE `DatabaseMGR`
  (
    `databaseID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `primCat` INT UNSIGNED NOT NULL, 
    `databaseName` VARCHAR(20),
     UNIQUE KEY (`databaseID`),
     PRIMARY KEY (`databaseID`)
)ENGINE = InnoDB;

---

To improve more:

• databaseID is already a primary key, so you do not have to make define it unique again, since: primary key = unique + not null
• Since MySQL is case insensitive, you should not use camel case names. So instead of databaseID, better to say database_id. There are more naming convention you can go through, though I will not mention here.

So for final table defination I suggest:

CREATE TABLE `database_mgr`
  (
    `database_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `prim_cat` INT UNSIGNED NOT NULL, 
    `database_name` VARCHAR(20),
     PRIMARY KEY (`databaseID`)
)ENGINE = InnoDB;