knex: Cannot create multiple PKs (mixin .increments with .primary)

Or you can create de autoincrement column with

table.specificType('myid','serial');

(only for Postgresql)

But I think is much better table.increments() will create the PK only if i manually specify that with table.increments().primary()

Any news on this?

@zacronos Took a quick look through the code. Looks trivial. Gonna throw together a PR https://github.com/DeedMob/knex/tree/master/src

This thread hasn’t gotten much attention in a while. I’d like to reiterate what I think is an ideal, backward-compatible solution (which can thus be part of any minor release): add a new option to .increments(), maybe primaryKey: false, which (for DBMSs where this is possible) disables the primary key part of its functionality.

This is entirely backward compatible since .increments() retains its default behavior.

This thread hasn’t gotten much attention in a while. I’d like to reiterate what I think is an ideal, backward-compatible solution (which can thus be part of any minor release): add a new option to .increments(), maybe primaryKey: false, which (for DBMSs where this is possible) disables the primary key part of its functionality.

This is entirely backward compatible since .increments() retains its default behavior.

Can we use increments like this? any update?

I have a similar issue – I want to use .increments() without it being a primary key. In my case it is for performance reasons. I want to create the table with no indexes (which means no primary key), insert many rows into it, and then add an index on the autoincrement column. This is more performant, and in fact is recommended by the Postgres documentation (see http://www.postgresql.org/docs/9.4/static/populate.html#POPULATE-RM-INDEXES), but currently seems impossible to do with knex.

Any solution?

Same issue here, having an extra parameter to disable PK seams reasonable.

In agreement with all the comments above. This behavior was unexpected when trying to create a composite primary key as well as a non-key auto-incrementing column. It would be great to see this implemented.

I fix the case when you have increments with multiple column primary key in some DB (pg and mssql). Mysql is quite different because you have 2 queries : one for create table and one for alter table primary keys. You cant create autoincrement column without primary key so I need to rework the code a little bit to solve this case. My goal is to take all cases in consideration without add new options.

Any news from this? I am using Knex 0.95.14 and I still face the same problem.

I raise the priority and take the task. I will try to take a look quickly.

Workaround:

table.bigincrements();
table.biginteger('other').notNullable();
table.dropPrimary();
table.primary(['id', 'other']);

Why not leave increments: 'serial' instead of increments: 'serial primary key'

Whats the problem with that?

Similar to #2896 you can use for MySQL:

t.primary(["cmdId", "deviceId"]);
t.specificType("cmdId", "int(10) unsigned AUTO_INCREMENT").notNullable();
t.string("deviceId", 16).notNullable().references("second_table.deviceId");

Same issue here .increments() tries to define a primary key, even if I manually specify one

No, but it probably should be. I’ll look into something for this.