Postgres: How to Do Composite Keys

Postgres: How to do Composite keys?

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)

What should I do about Composite Key?

Well, it's easier than I thought :-) Just put 2 parameters into RouteValues in ActionLink so it generates a query string.

@Html.ActionLink("Edit", "Edit", new { PoNo= Model.PoNo, ItemNo= Model.ItemNo })

The url will be Item/Edit/2?ItemNo=4

So it's more about routing than anything else in my question. More on this

PostgreSQL composite primary key

If you create a composite primary key, on (x, y, z), PostgreSQL implements this with the help of one UNIQUE multi-column btree index on (x, y, z). In addition, all three columns are NOT NULL (implicitly), which is the main difference between a PRIMARY KEY and a UNIQUE INDEX.

Besides obvious restrictions on your data, the multi-column index also has a somewhat different effect on the performance of queries than three individual indexes on x, y and z.

Related discussion on dba.SE:

• Working of indexes in PostgreSQL

With examples, benchmarks, discussion and outlook on the new feature of index-only scans in Postgres 9.2.

In particular, a primary key on (x, y, z) will speed up queries with conditions on x, (x,y) or (x,y,z) optimally. It will also help with queries on y, z, (y,z) or (x,z) but to a far lesser extent.

If you need to speed up queries on the latter combinations, you may want to change the order of column in your PK constraint and/or create one or more additional indexes. See:

• Is a composite index also good for queries on the first field?

Does indexing on a part of a composite primary key is needed in Postgresql?

For best performance, you would need an additional index on (a, e).

If that is an option, you can drop the primary key and replace it with one on (a, e, b, c, d). The primary key index would then support your query, and it is just as good as far as uniqueness is concerned.

You can also experiment with a single-column index on (e). PostgreSQL can combine scans on several indexes. That won't be as fast as a multi-column index, but the resulting index would be smaller.

Composite primary key and slow queries

An index can only be used efficiently if the first index column is used in the WHERE condition (yes, I know there are exceptions, but it is a good rule of thumb).

So to support that query, you should define the primary key the other way around, as (user_id, code). That will guarantee the constraint just as well, but the underlying index will support your query.

If you cannot change the primary key like that, you need another index on user_id alone.

But then, looking on the Rows Removed by Filter, probably a sequential scan is the fastest access strategy, and an index won't help at all. Try it out.

Unique partial composite primary key in Postgres

You can use the INCLUDE option to add extra columns in the index that are not actually part of the index itself.

create table foo (
  id integer not null,
  yesno boolean not null,
  extra text
);
Create unique index foo_uk 
           on foo (id, yesno) 
      include (extra);

You did not indicate what Postgres version you have, so this may not be appropriate, as you need at least version 11.

Does Prisma support composite keys with one of the attribute being NULL for PostgreSQL?

No, Prisma doesn't support composite keys with optional columns. ID Definitions are required.

Example:

This would work

model Post {
  title   String
  content String

  @@id([title, content])
}

But this wouldn't as column content is defined as optional

model Post {
  title   String
  content String?

  @@id([title, content])
}

You can create a Feature Request here to support allowing nullable columns in composite id.

---

Related Topics