Adding a Foreign Key from a SQL Dump fails

Question

I'm trying to create a database from an SQL Dump. I wrote it like this:

CREATE DATABASE IF NOT EXISTS rsa_database;

CREATE TABLE IF NOT EXISTS `keys` (
  `id` int(11) NOT NULL auto_increment,
  `public_key` varchar(20) collate latin1_general_ci NOT NULL,
  `private_key` varchar(30) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(20) collate latin1_general_ci NOT NULL,
  `lastname` varchar(20) collate latin1_general_ci NOT NULL,
  `date_of_birth` varchar(20) collate latin1_general_ci NOT NULL,
  `zip` varchar(20) collate latin1_general_ci NOT NULL,
  `city` varchar(20) collate latin1_general_ci NOT NULL,
  `street` varchar(20) collate latin1_general_ci NOT NULL,
  `number` varchar(20) collate latin1_general_ci NOT NULL,
  `tel` varchar(20) collate latin1_general_ci NOT NULL,
  `email` varchar(20) collate latin1_general_ci NOT NULL,
  `k_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
    FOREIGN KEY (k_id) REFERENCES keys(id)
);

But my MySQL Server throws this error message:

ERROR 1064 (42000) at line 78: 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(id)' at line 1

I tried different notations and read some documentations, but I cant figure out my failure.
Thank you!

Answer 1

Since keys is a reserved word in MySQL, you need to quote it with backticks every time you reference the table named keys:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(20) collate latin1_general_ci NOT NULL,
  `lastname` varchar(20) collate latin1_general_ci NOT NULL,
  `date_of_birth` varchar(20) collate latin1_general_ci NOT NULL,
  `zip` varchar(20) collate latin1_general_ci NOT NULL,
  `city` varchar(20) collate latin1_general_ci NOT NULL,
  `street` varchar(20) collate latin1_general_ci NOT NULL,
  `number` varchar(20) collate latin1_general_ci NOT NULL,
  `tel` varchar(20) collate latin1_general_ci NOT NULL,
  `email` varchar(20) collate latin1_general_ci NOT NULL,
  `k_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  FOREIGN KEY (`k_id`) REFERENCES `keys`(`id`)
);