Postgres: How to do Composite keys?

Question

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?

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

Remove the line that's giving you a syntax error, and also remove the redundant CONSTRAINT like this:

 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)

Note: The compound PRIMARY KEY specification already does what you want.