SQL Postgres：如何做复合键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1285967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: How to do Composite keys?
提问by Léo Léopold Hertz ??
I cannot understand the syntax error in creating a composite key. It may be a logic error, because I have tested many varieties.
我无法理解创建复合键时的语法错误。可能是逻辑错误，因为我测试了很多品种。
How do you create composite keys in Postgres?
你如何在 Postgres 中创建复合键？
CREATE TABLE tags
(
(question_id, tag_id) NOT NULL,
question_id INTEGER NOT NULL,
tag_id SERIAL NOT NULL,
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20),
PRIMARY KEY(question_id, tag_id),
CONSTRAINT no_duplicate_tag UNIQUE (question_id, tag_id)
);
ERROR: syntax error at or near "("
LINE 3: (question_id, tag_id) NOT NULL,
^
回答by pilcrow
Your compound PRIMARY KEYspecification already does what you want. Omit the line that's giving you a syntax error, and omit the redundant CONSTRAINT(already implied), too:
您的化合物PRIMARY KEY规格已经满足您的要求。省略给你一个语法错误的行，也省略多余的CONSTRAINT（已经暗示）：
CREATE TABLE tags
(
question_id INTEGER NOT NULL,
tag_id SERIAL NOT NULL,
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20),
PRIMARY KEY(question_id, tag_id)
);
NOTICE: CREATE TABLE will create implicit sequence "tags_tag_id_seq" for serial column "tags.tag_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tags_pkey" for table "tags"
CREATE TABLE
pg=> \d tags
Table "public.tags"
Column | Type | Modifiers
-------------+-----------------------+-------------------------------------------------------
question_id | integer | not null
tag_id | integer | not null default nextval('tags_tag_id_seq'::regclass)
tag1 | character varying(20) |
tag2 | character varying(20) |
tag3 | character varying(20) |
Indexes:
"tags_pkey" PRIMARY KEY, btree (question_id, tag_id)
回答by Léo Léopold Hertz ??
The error you are getting is in line 3. i.e. it is not in
你得到的错误在第 3 行。即它不在
CONSTRAINT no_duplicate_tag UNIQUE (question_id, tag_id)
but earlier:
但早些时候：
CREATE TABLE tags
(
(question_id, tag_id) NOT NULL,
Correct table definition is like pilcrow showed.
正确的表定义就像 pilcrow 所示。
And if you want to add unique on tag1, tag2, tag3 (which sounds very suspicious), then the syntax is:
如果你想在 tag1、tag2、tag3 上添加 unique（听起来很可疑），那么语法是：
CREATE TABLE tags (
question_id INTEGER NOT NULL,
tag_id SERIAL NOT NULL,
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20),
PRIMARY KEY(question_id, tag_id),
UNIQUE (tag1, tag2, tag3)
);
or, if you want to have the constraint named according to your wish:
或者，如果您想根据自己的意愿命名约束：
CREATE TABLE tags (
question_id INTEGER NOT NULL,
tag_id SERIAL NOT NULL,
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20),
PRIMARY KEY(question_id, tag_id),
CONSTRAINT some_name UNIQUE (tag1, tag2, tag3)
);
