CODEWITHSUNDEEP
mysqldatabase-designbackticks
FDaniels
FDaniels

Reputation: 23

MySQL error involving backticks

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.

Upvotes: 1

Views: 801

Answers (1)

Musa Haidari
Musa Haidari

Reputation: 2267

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;

Upvotes: 1

Related Questions