英文:
why references not working when using dbschema tool?
问题
我使用DBSCHEMA工具，并想执行查询，但我收到以下错误消息：
在“id”附近的语法错误
位置209
代码：
CREATE TABLE user_info (
id UUID PRIMARY KEY,
full_name VARCHAR NOT NULL,
birthday VARCHAR NOT NULL,
sex VARCHAR NOT NULL,
profile_image VARCHAR,
verified SMALLINT DEFAULT 0,
legend VARCHAR NULL,
FOREIGN KEY id REFERENCES user(id)
)
英文:
I use DBSCHEMA tool and want to execute a query but I got this error:
syntax error at or near "id"
position 209
Code:
CREATE TABLE user_info (
id UUID PRIMARY KEY,
full_name VARCHAR NOT NULL,
birthday VARCHAR NOT NULL,
sex VARCHAR NOT NULL,
profile_image VARCHAR,
verified SMALLINT DEFAULT 0,
legend VARCHAR NULL,
FOREIGN KEY id REFERENCES user(id)
)
答案1
得分: 1
根据文档，用于约束的列应该放在括号内；另外，user 应该用引号括起来。
FOREIGN KEY (id) REFERENCES "user"(id)
英文:
According to the documentation the column(s) defined for the constraint should be in parenthesis; in addition, user should be delimited.
FOREIGN KEY (id) REFERENCES "user"(id)
答案2
得分: 1
以下是您代码中需要翻译的部分：
首先，这不是有效的SQL：
FOREIGN KEY id REFERENCES user(id)
列名需要用括号括起来（请注意，外键可能涉及多列）：
FOREIGN KEY (id) REFERENCES user(id)
然后，user 在Postgres中是一个保留字，所以在用作标识符时需要用引号括起来，这会使标识符区分大小写。假设您创建了这个表时全部使用小写字母，您可以这样做：
FOREIGN KEY (id) REFERENCES "user"(id)
但如果有机会的话，最好不要使用保留字作为表名：我建议如果可能的话将表名重命名为其他名称。
英文:
There are several problems with your code.
First, this is not valid SQL:
FOREIGN KEY id REFERENCES user(id)
The column(s) needs to be surrounded with parenthesis (keep in mind that a foreign key could involve more than one column):
FOREIGN KEY (id) REFERENCES user(id)
Then: user is a reserved word in Postgres, so it needs be quoted when used as an identifier - which has the side effect of making the identifier case-sensitive. Assuming that you created this table as all lower-case, you would do:
FOREIGN KEY (id) REFERENCES "user"(id)
But it would really be a better idea not to use a reserved word as table name: I would recommend renaming the table to something else if you have a chance to.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论