title: Knex : Altering Primary Key Id column Error
body: <p>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.</p>
  
  <blockquote>
    <p>alter table "users" alter column "id" drop not null - column "id" is
    in a primary key</p>
  </blockquote>
  
  <pre><code>exports.up = knex =&gt;
    knex.schema
      .raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
      .createTable('users', table =&gt; {
        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 =&gt; knex.schema.dropTable('users');
  </code></pre>
  
  <pre><code>exports.up = knex =&gt;
    knex.schema.alterTable('users', table =&gt; {
      table
        .uuid('id')
        .unique()
        .primary()
        .notNullable()
        .defaultTo(knex.raw('uuid_generate_v4()'))
        .alter();
    });
  
  exports.down = knex =&gt;
    knex.schema.table('users', table =&gt; {
      table.dropColumn('id').alter();
    });
  </code></pre>
  
  <blockquote>
    <p>PostgreSQL version : 11.1 </p>
    
    <p>Knex version : 0.19.2</p>
  </blockquote>
  
  <p>I have searched here and there but couldn't find an answer for this issue.
  Thanks for taking your time to help me out !</p>
  
  <p>------------------------------ 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.</p>
  
  <blockquote>
    <p>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"</p>
  </blockquote>
  
  <pre><code>exports.up = knex =&gt;
    knex.schema
      .raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
      .createTable('delivery', table =&gt; {
        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 =&gt; knex.schema.dropTable('delivery');
  
  ```
  </code></pre>