Syntax error on adding multiple indexes on Table Creation mysql

Question

I am trying to run a CREATE TABLE script which has multiple INDEXES.

CREATE TABLE IF NOT EXISTS Equipment (
    EquipmentID BIGINT  UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    Type VARCHAR(255) NOT NULL,
    Make VARCHAR(255),
    Model VARCHAR(255),
    Description VARCHAR(255),
    OperationNotes TEXT,
    Damaged BOOLEAN DEFAULT 0,
    PRIMARY KEY (EquipmentID),
    INDEX ('EquipmentID'),
    INDEX('Type'),
    INDEX('Model'),
    INDEX('Description')
    INDEX('Damaged')
);

However I get a syntax error:

"(" is not valid at this position for this server version

On line:

    INDEX ('EquipmentID'),

INDEX('Description') is missing a comma and please read https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql — P.Salmon, Mar 07 '20 at 10:52

Mureinik · Accepted Answer · 2020-03-07T10:53:36.810

Single quote (') denote string literals. When referring to object names (such as columns), you shouldn't use single quotes. Remove them, and you should be OK. Also, note that a primary key implicitly creates an index, so you don't need to explicitly create an index on EquipmentID:

CREATE TABLE IF NOT EXISTS Equipment (
    EquipmentID BIGINT  UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    Type VARCHAR(255) NOT NULL,
    Make VARCHAR(255),
    Model VARCHAR(255),
    Description VARCHAR(255),
    OperationNotes TEXT,
    Damaged BOOLEAN DEFAULT 0,
    PRIMARY KEY (EquipmentID),
    INDEX (Type),
    INDEX (Model),
    INDEX (Description),
    INDEX (Damaged)
);

Syntax error on adding multiple indexes on Table Creation mysql

1 Answers1