CODEWITHSUNDEEP
postgresqlknex.js
Jiaah
Jiaah

Reputation: 834

Knex : Altering Primary Key Id column Error

The database is built on Knex / PostgreSQL. I would like to alter a table to add 'unique()' type to primary key Id column. But Migration failed with an error message below.

alter table "users" alter column "id" drop not null - column "id" is in a primary key

exports.up = knex =>
  knex.schema
    .raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
    .createTable('users', table => {
      table
        .uuid('id')
        .primary()
        .notNullable()
        .defaultTo(knex.raw('uuid_generate_v4()'));
      table
        .string('companyName')
        .unique()
        .notNullable();
      table
        .string('username')
        .unique()
        .notNullable();
      table.string('password').notNullable();
      table.string('contactNo').notNullable();
      table.string('email').unique();
      table.string('address');
      table
        .boolean('isAdmin')
        .notNullable()
        .defaultTo(false);
      table
        .enum('businessType', ['catering', 'restaurant'])
        .defaultTo('catering');
      table.integer('lunchQty').defaultTo(null);
      table.integer('dinnerQty').defaultTo(null);
      table
        .uuid('bankAccountId')
        .references('id')
        .inTable('bank_account')
        .onDelete('SET NULL')
        .onUpdate('RESTRICT')
        .index();
      table.string('resetPasswordToken');
      table.timestamps(true, true);
    });

exports.down = knex => knex.schema.dropTable('users');

exports.up = knex =>
  knex.schema.alterTable('users', table => {
    table
      .uuid('id')
      .unique()
      .primary()
      .notNullable()
      .defaultTo(knex.raw('uuid_generate_v4()'))
      .alter();
  });

exports.down = knex =>
  knex.schema.table('users', table => {
    table.dropColumn('id').alter();
  });

PostgreSQL version : 11.1

Knex version : 0.19.2

I have searched here and there but couldn't find an answer for this issue. Thanks for taking your time to help me out !

------------------------------ EDITION ----------------------------------- Qustion ) when I created delivery table like below. The error below accurred. I thought this was caused because I didn't set primary key unique.

migration failed with error: alter table "delivery" add constraint "delivery_userid_foreign" foreign key ("userId") references "users" ("id") on update RESTRICT on delete CASCADE - there is no unique constraint matching given keys for referenced table "users"

exports.up = knex =>
  knex.schema
    .raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
    .createTable('delivery', table => {
      table
        .uuid('id')
        .primary()
        .notNullable()
        .defaultTo(knex.raw('uuid_generate_v4()'));
      table
        .uuid('routeId')
        .references('id')
        .inTable('routes')
        .onDelete('CASCADE')
        .onUpdate('RESTRICT')
        .index();
      table
        .uuid('userId')
        .references('id')
        .inTable('users')
        .onDelete('CASCADE')
        .onUpdate('RESTRICT')
        .index();
      table.timestamps(true, true);
    });

exports.down = knex => knex.schema.dropTable('delivery');

```

Upvotes: 1

Views: 5611

Answers (2)

Jiaah
Jiaah

Reputation: 834

resolved the issue by removing alter users file !! the 'unique key()' was the one that was causing the problem.

Upvotes: 0

Rich Churcher
Rich Churcher

Reputation: 7654

Primary keys are already unique and not null: there is no need for your alteration. See documentation. Knex is trying to do what you ask, but in order to alter the table it would have to drop id which is in a PRIMARY KEY constraint.

Upvotes: 2

Related Questions