mysql - 尝试使用外键创建表时出错

Question

我正在制作一个简单的 3 表数据库。但是，当我尝试创建 prod_owners 表时，我不断收到错误消息。我不明白为什么。我试着在 w3school 上查找它。按照它的格式，我还是弄错了。有人可以解释我做错了什么吗？以及我该如何修复它。

错误:

Static analysis:

3 errors were found during analysis.

A comma or a closing bracket was expected. (near "FOREIGN KEY" at position 236)
Unexpected beginning of statement. (near "user_id" at position 249)
Unrecognized statement type. (near "REFERENCES" at position 258)
SQL query:

CREATE TABLE prod_owners ( owner_id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id int(11) NOT NULL FOREIGN KEY (user_id) REFERENCES users (user_id), prod_id int(20) NOT NULL FOREIGN KEY (prod_id) REFERENCES products (prod_id) )

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOREIGN KEY (user_id) REFERENCES users (user_id),
    prod_id         int(20)
' at line 8

---

代码:

CREATE TABLE users (
    user_id        int(11)
                    NOT NULL
                    AUTO_INCREMENT
                    PRIMARY KEY,
    phone           int(11)
                    NOT NULL
);

CREATE TABLE products (
    prod_id         int(20)
                    NOT NULL
                    AUTO_INCREMENT
                    PRIMARY KEY,
    info            varchar(1000)
                    NOT NULL
);

CREATE TABLE prod_owners (
    owner_id        int(20)
                    NOT NULL
                    AUTO_INCREMENT
                    PRIMARY KEY,
    user_id         int(11)
                    NOT NULL
                    FOREIGN KEY (user_id) REFERENCES users (user_id),
    prod_id         int(20)
                    NOT NULL
                    FOREIGN KEY (prod_id) REFERENCES products (prod_id)
);

Answer 1

您需要在列定义后添加逗号。

看下面的代码:

CREATE TABLE users(
  user_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  phone INT(11) NOT NULL
);
CREATE TABLE products(
  prod_id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  info VARCHAR(1000) NOT NULL
);
CREATE TABLE prod_owners(
  owner_id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT(11) NOT NULL, FOREIGN KEY(user_id) REFERENCES users(user_id),
  prod_id INT(20) NOT NULL, FOREIGN KEY(prod_id) REFERENCES products(prod_id)
);