CODEWITHSUNDEEP
postgresqlknex.js
Constantinos N
Constantinos N

Reputation: 263

Change primary key and its (foreign) references in existing table - Knex.js / Postgres

My Postgres DB has 2 tables, with thousands of rows each, that were initially created with the following migration:

exports.up = async function(knex, Promise) {
  // users
  await knex.schema.createTable('users', table => {
    table.increments('id');
    table.timestamps(false, true);
    table.text('uid').notNullable().unique(),
    table.text('email').notNullable();
    table.text('password');
    table.text('first_name').notNullable();
    table.text('last_name').notNullable();
    table.text('subscription_id');
    table.boolean('is_active').notNullable().defaultTo(true);
    table.boolean('is_blocked').notNullable().defaultTo(false);
    table.enum('role', ['member', 'admin', 'test_user']).notNullable().defaultTo('member');
  });
  await knex.schema.raw('create unique index users_lower_email_index on users (lower(email))');

  // projects
  await knex.schema.createTable('projects', table => {
    table.increments('id');
    table.timestamps(false, true);
    table.text('name').notNullable();
    table.integer('user_id').notNullable().references('users.id').onDelete('cascade');
    table.text('data');
  });
};

I need to change the foreign key on the projects table so that it references the uid column instead from the users table.

The constraints on the users table are: enter image description here

I tried the following migration but I get the error:

migration failed with error: alter table "users" add column "uid" text - column "uid" of relation "users" already exists

My code:


exports.up = async function(knex, Promise) {
    await knex.schema.alterTable('users', table => {
        table.text('uid').primary('users_pkey');
    })

    await knex.schema.alterTable('projects', table => {
        table.text('user_id').notNullable().references('users.uid').onDelete('cascade').alter();
    });
};

I also tried table.text('uid').primary('users_pkey').alter(); but then I get:

migration failed with error: alter table "users" add constraint "users_pkey" primary key ("uid") - multiple primary keys for table "users" are not allowed

I will transfer all users in auth0 and I though its better if I use a UUID primary key for the users table.

Upvotes: 1

Views: 8826

Answers (1)

Parker
Parker

Reputation: 520

Before you can change the primary key of users, you need to remove the existing one, then you should be able to drop and recreate the foreign key in projects:

exports.up = async (knex) => {
  await knex.schema.alterTable('users', (table) => {
    table.dropPrimary()
    table.primary('uid')
  })

  await knex.schema.alterTable('projects', (table) => {
    table.dropForeign('user_id')
    table.foreign('user_id').references('users.uid').onDelete('cascade')
  })
}

Upvotes: 6

Related Questions