Alter Table gives error #1064

Question

I am trying to create two tables in phpmyadmin: Users and Keys, the schema is as follows:

Users:

id int auto_incerement primary key

name varchar(50) not null

Keys:

user_id int

keys varchar(50) not null

Now I am running the following query to make the user_id in Keys a foreign key referencing the id in Users

ALTER TABLE Keys
FOREIGN KEY(user_id) REFERENCES Users(id)
ON UPDATE CASCADE
ON DELETE CASCADE

But after executing, i am getting the following 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 'Keys FOREIGN KEY(user_id) REFERENCES Users(id) ON UPDATE CASCADE ON DELETE CA' at line 1

Can anyone tell me what am I doing wrong?

Thanks in advance.

Just throwing it out there...you say you're using phpMyAdmin, but you're working with SQL statements. Everything you're doing above can be done graphically from phpMyAdmin (which does properly escape table names and reserved words and so on. There's nothing wrong with typing the SQL statement, but it's also easy to do these things graphically. phpMyAdmin has several tools to work with relations (Relation View, Designer, etc). — Isaac Bennetch, Oct 13 '13 at 05:05
i know it is easy to use the GUI, but where's the fun in that? ;) — R4chi7, Oct 20 '13 at 06:18

Kermit · Accepted Answer · 2013-10-08T14:20:41.167

3

Keys is a reserved word and must be encased in backticks:

ALTER TABLE `Keys`
ADD FOREIGN KEY (user_id) REFERENCES `Users` (`id`)
ON UPDATE CASCADE
ON DELETE CASCADE

It's best practice to encase all system related words in backticks.

edited Oct 08 '13 at 14:20

answered Oct 08 '13 at 14:13

Kermit

33,827
13
85
121

thanks for the quick reply. I changed the query to what you have written, it is still giving me the 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 'FOREIGN KEY(\`user_id\`) REFERENCES Users(\`id\`) ON UPDATE CASCADE ON DELETE CASC' at line 2` – R4chi7 Oct 08 '13 at 14:19
Try dropping the backticks around the foreign key name. – Kermit Oct 08 '13 at 14:20
doesn't work still :/ – R4chi7 Oct 08 '13 at 14:21
Are you receiving the same error? – Kermit Oct 08 '13 at 14:22
this is the 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 'FOREIGN KEY(user_id) REFERENCES \`Users\`(\`id\`) ON UPDATE CASCADE ON DELETE CASC' at line 2` – R4chi7 Oct 08 '13 at 14:23
1

We're missing the `ADD` before `FOREIGN KEY`.... – Kermit Oct 08 '13 at 14:24
the query didn't give an error, but neither did it show any confirmation, is there any way i could check if it succeeded? – R4chi7 Oct 08 '13 at 14:26
1

Run `SHOW CREATE TABLE *Keys*` (replace `*` with a backtick) – Kermit Oct 08 '13 at 14:27

score 2 · Answer 2 · answered Oct 08 '13 at 14:13

2

KEYS is a reserved keyword. See also: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You need to wrap them in backticks:

ALTER TABLE `Keys`
...

answered Oct 08 '13 at 14:13

bwoebi

23,637
5
58
79

score 2 · Answer 3 · answered Oct 08 '13 at 14:18

2

You need to enclose keys with back tick.

ALTER TABLE `Keys`

answered Oct 08 '13 at 14:18

Sathish D

4,854
31
44

Alter Table gives error #1064

3 Answers3