从SQL转储添加外键失败

从SQL转储添加外键失败,第1张

我试图从SQL Dump创建数据库。 我是这样写的:

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)
);

但我的MySQL服务器抛出此错误消息:

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

我尝试了不同的符号并阅读了一些文档,但我无法弄清楚我的失败。
谢谢!

最佳答案:

1 个答案:

答案 0 :(得分:2)

由于keys在MySQL中是reserved word,因此每次引用名为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`)
);
本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复