CODEWITHSUNDEEP
mysqlindexingkey
Kittencakes
Kittencakes

Reputation: 9

Having Trouble with MySQL Primary Key and adding Index (Error #1064)

CREATE TABLE registers(
User_ID INT UNSIGNED NOT NULL PRIMARY KEY,
IP INT UNSIGNED NOT NULL INDEX,
Success TINYINT UNSIGNED NOT NULL,
Time_Created DATETIME NOT NULL)

I get this 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, Success TINYINT UNSIGNED NOT NULL, Time_Created DATETIME NOT NULL)' at line 3

Upvotes: 0

Views: 534

Answers (3)

phpPhil
phpPhil

Reputation: 926

Try this instead - in MySQL syntax the INDEX definition cannot be included in the field definition:

CREATE TABLE registers(
User_ID INT UNSIGNED NOT NULL PRIMARY KEY,
IP INT UNSIGNED NOT NULL,
Success TINYINT UNSIGNED NOT NULL,
Time_Created DATETIME NOT NULL,
INDEX `ip_1` (`ip`)
)

Alternatively - sometimes preferred for readability you can create the index in a separate statement after you create the table:

CREATE TABLE registers(
User_ID INT UNSIGNED NOT NULL PRIMARY KEY,
IP INT UNSIGNED NOT NULL,
Success TINYINT UNSIGNED NOT NULL,
Time_Created DATETIME NOT NULL
);

CREATE INDEX ip_1 ON registers (ip);

Upvotes: 0

Jens
Jens

Reputation: 69470

Your synatx must be:

CREATE TABLE registers(
User_ID INT UNSIGNED NOT NULL PRIMARY KEY,
IP INT UNSIGNED NOT NULL ,
Success TINYINT UNSIGNED NOT NULL,
Time_Created DATETIME NOT NULL,
INDEX (IP )
)

For more information about the create table statement see the official mysql documentation

Upvotes: 0

StuartLC
StuartLC

Reputation: 107357

To define inline indexes in a CREATE TABLE statement, you will need to place it on a separate 'create definition' line:

CREATE TABLE registers(
  User_ID INT UNSIGNED NOT NULL PRIMARY KEY,
  IP INT UNSIGNED NOT NULL,
  INDEX(IP),
  Success TINYINT UNSIGNED NOT NULL,
  Time_Created DATETIME NOT NULL
);

SqlFiddle

You can also place the index definition outside of the table definition, like so:

CREATE INDEX IX_registers_ip ON registers(IP);

It is regarded as good practice to name the index, so that it can be referenced exactly.

Upvotes: 1

Related Questions