19

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');
});
William Desportes
  • 1,412
  • 1
  • 22
  • 31
Coder1234
  • 389
  • 1
  • 3
  • 9

3 Answers3

30

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']);
William Desportes
  • 1,412
  • 1
  • 22
  • 31
Yazan Khalaileh
  • 589
  • 6
  • 15
9

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']);
});
William Desportes
  • 1,412
  • 1
  • 22
  • 31
SunshinyDoyle
  • 3,441
  • 1
  • 18
  • 21
0

In your case, I would have left the existing increments field as the primary key, and added an unique index on the foreign key after the table was created.

knex.schema.alterTable('tickets', function(t) {
    t.unique(['events_id'])
})

If you want a composite key, do this:

knex.schema.alterTable('tickets', function(t) {
    t.unique(['id','events_id'])
})
William Desportes
  • 1,412
  • 1
  • 22
  • 31
GaryL
  • 1,385
  • 8
  • 11