Postgres: How to make compound keys?

Question

I cannot understand the syntax error while creating the composite key. This could be a logical mistake, because I tested many varieties.

How do you create compound keys in 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, ^ 

Answer 1

Your composite PRIMARY KEY specification is already doing what you want. Omit the line giving you the syntax error, and omit the redundant CONSTRAINT (already implied) too:

  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) 

Answer 2

The error you get is on line 3. i.e. she is not in

 CONSTRAINT no_duplicate_tag UNIQUE (question_id, tag_id) 

but earlier:

 CREATE TABLE tags ( (question_id, tag_id) NOT NULL, 

I have absolutely no idea why you put it - what is the purpose? What is the logic?

Anyway. A proper table definition is similar to a sawtooth.

And if you want to add a unique tag tag1, tag2, tag3 (which sounds very suspicious), then the syntax:

 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 a restriction specified in accordance with your desire:

 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) );