Kyle.Belanger Kyle.Belanger - 4 months ago 11
MySQL Question

Table named 'Index'

I have a table named 'Index'. I realize that this is a keyword in MySQL, and was wondering how I can reference this table in queries?

Here is my current schema:

CREATE TABLE `Index`
(
ID INT PRIMARY KEY AUTO_INCREMENT,
IsElectronic BOOLEAN,
IsAvailable BOOLEAN,

INDEX(uID)
);

CREATE TABLE `grants`
(
ID INT PRIMARY KEY AUTO_INCREMENT,
RecipientBusinessName VARCHAR(50),
Index_fk INT,

CONSTRAINT grants_fk_index
FOREIGN KEY (Index_fk)
references 'Index' (ID)
);


My 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 ''Index' (ID) )' at line 9

Answer

Use backticks:

references `Index` (ID)

You actually already did this when you created the Index table (and you must have used backticks or else you could not have created the table):

CREATE TABLE `Index`

You should avoid naming databases, tables, and columns using MySQL keywords.