CODEWITHSUNDEEP
postgresqlknex.jscomposite-primary-key
Coder1234
Coder1234

Reputation: 409

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

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

Upvotes: 21

Views: 21394

Answers (3)

Yazan Khalaileh
Yazan Khalaileh

Reputation: 639

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

Upvotes: 33

GaryL
GaryL

Reputation: 1460

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'])
})

Upvotes: 0

SunshinyDoyle
SunshinyDoyle

Reputation: 3584

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']);
});

Upvotes: 9

Related Questions