mysql - #1064 - 您的 SQL 语法错误无法更改表以创建外键

Question

您好，我是 mysql 的新手，所以请帮助我。我正在尝试更改一个名为 order 的表并在其中创建一个外键。有一个字段 user_id 是这个表，我想给它分配外键这是我正在使用的查询

alter table order
add constraint foreign key u_id(user_id) references user (user_id) on update cascade on delete cascade;

有一个以 user_id 作为主键的用户表，我收到此错误。

#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 'order add constraint foreign key u_id(user_id) references user (user_id)' at line 1

有人可以指出我在哪里犯了错误，因为我无法找出问题所在。

Answer 1

ORDER 是 Reserved Word .如 Schema Object Names 下所述:

If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)

[ deletia ]

The identifier quote character is the backtick (“`”):

mysql> <strong>SELECT * FROM `select` WHERE `select`.id > 100;</strong>

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:

mysql> <strong>CREATE TABLE "test" (col INT);</strong>
ERROR 1064: You have an error in your SQL syntax...
mysql> <strong>SET sql_mode='ANSI_QUOTES';</strong>
mysql> <strong>CREATE TABLE "test" (col INT);</strong>
Query OK, 0 rows affected (0.00 sec)

因此:

ALTER TABLE `order`
  ADD CONSTRAINT FOREIGN KEY u_id (user_id)
                 REFERENCES  user (user_id)
                          ON UPDATE CASCADE
                          ON DELETE CASCADE;