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,
                          ^

Solution 1

Your compound PRIMARY KEY specification already does what you want. Omit the line that's giving you a 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)

Solution 2

The error you are getting is in line 3. i.e. it is not in

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.

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

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

Comments

[Michal aka Miki]

How would you implement a contraint like "CONSTRAINT no_duplicate_refences UNIQUE REFERENCE FROM tag_id TO (tag1, tag2, tag3)"?

[pilcrow]

@Masi, I don't think I understand enough of what you're trying to model here, and, to be frank, the columns tag1 through tag3 suggest to me that you might have further design refinements to do. Perhaps a separate question, with a natural language description of your model and a few example records, would help.