英文:
Syntax error when adding a new column with a foreign key
问题
我试图向表public.languages添加四列，其中两列引用了public.users表。
ALTER TABLE languages
ADD created_by INTEGER NOT NULL,
ADD created_at TIMESTAMP NOT NULL,
ADD modified_by INTEGER NOT NULL,
ADD modified_at TIMESTAMP NOT NULL,
FOREIGN KEY(created_by, modified_by) REFERENCES users(user_id);
我可以从public.users表中执行SELECT *，并且user_id列显示正常。
ERROR: 语法错误，靠近“FOREIGN”
行 6: FOREIGN KEY(created_by, modified_by) REFERENCES users(user_...
英文:
I'm trying to add four columns to a table, public.languages, where two of these columns reference the public.users table.
ALTER TABLE languages
ADD created_by INTEGER NOT NULL,
ADD created_at TIMESTAMP NOT NULL,
ADD modified_by INTEGER NOT NULL,
ADD modified_at TIMESTAMP NOT NULL,
FOREIGN KEY(created_by, modified_by) REFERENCES users(user_id);
I can SELECT * from public.users and the user_id column appears just fine.
ERROR: syntax error at or near "FOREIGN"
LINE 6: FOREIGN KEY(created_by, modified_by) REFERENCES users(user_...
答案1
得分: 0
以下是翻译好的部分：
有两个问题。一个是您试图在两个列上创建外键约束，而不是在每个列上创建两个约束。另一个问题是您的语法无效 - 将约束与列一起放在ALTER TABLE中不起作用，而是应该使用CREATE TABLE。您可以通过以下方式修复这两个问题：
ALTER TABLE languages
ADD created_by INTEGER NOT NULL,
ADD created_at TIMESTAMP NOT NULL,
ADD modified_by INTEGER NOT NULL,
ADD modified_at TIMESTAMP NOT NULL,
ADD CONSTRAINT asdfasf FOREIGN KEY (created_by) REFERENCES users(user_id),
ADD CONSTRAINT asdfasf2 FOREIGN KEY (modified_by) REFERENCES users(user_id);
英文:
There are two issues there. One is that you are trying to create a foreign key constraint on two columns, rather than two constraints on one column each. The other is that your syntax is invalid--just throwing the constraints in with the columns works with CREATE TABLE, not with ALTER TABLE. You can fix both of those giving:
ALTER TABLE languages
ADD created_by INTEGER NOT NULL,
ADD created_at TIMESTAMP NOT NULL,
ADD modified_by INTEGER NOT NULL,
ADD modified_at TIMESTAMP NOT NULL,
ADD constraint asdfasf FOREIGN KEY (created_by) REFERENCES users(user_id),
ADD constraint asdfasf2 FOREIGN KEY (modified_by) REFERENCES users(user_id);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论