How do I add a Composite primary key with Knex.js?

Question

I have 2 tables. 1 called events with an event ID and another table called tickets which I want to have primary keys of event ID and ticket ID. I am also using a PostgreSQL database. At the moment ,I have it as a foreign key but would like to have it as a primary key in the ticket table with the ticket ID.

knex.schema.createTable('events', function (table) {
    table.increments('id');
    table.string('eventName');
});

knex.schema.createTable('tickets', function (table) {
    table.increments('id');
    table.string('ticketName');

    table.foreign('tickets').references('events_id').inTable('events');
});

Answer 1

As per Knex's documentation here:

primary — column.primary([constraintName]); table.primary(columns, [constraintName]) When called on a single column it will set that column as the primary key for a table. If you need to create a composite primary key, call it on a table with an array of column names instead. Constraint name defaults to tablename_pkey unless constraintName is specified.

Therefore, in your case you could add:

table.primary(['name_of_column_1', 'name_of_column_2']);

Answer 2

Using your example I think you've got 2 options:

Option 1 (use the id as primary key and add a unique constraint):

knex.schema.createTable('events', function (table) {
    table.increments('id').primary();
    table.string('eventName');
});


knex.schema.createTable('tickets', function (table) {
    table.increments('id').primary();
    table.string('ticketName');

    table.integer('event_id').references('id').inTable('events');

    table.unique(['id', 'event_id']);
});

Options 2 (Use the two ids as composite primary keys):

knex.schema.createTable('events', function (table) {
    table.increments('id').primary();
    table.string('eventName');
});


knex.schema.createTable('tickets', function (table) {
    table.increments('id');
    table.string('ticketName');

    table.integer('event_id').references('id').inTable('events');

    table.primary(['id', 'event_id']);
});