How can I update my column to make it unique ?

[Hakamate]

Hello everyone,

So I made a migration like that :

  protected tableName = 'users'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').primary()
      table.string('email', 255).notNullable()
      table.string('password', 180).notNullable()
      table.string('remember_me_token').nullable()
      table.timestamps(true)
    })
  }

But I want to make "email" column unique :

make:migration add_unique_to_email --table=users

That generate :

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Users extends BaseSchema {
  protected tableName = 'users'

  public async up () {
    this.schema.table(this.tableName, (table) => {
      table.string('email', 255).unique().notNullable()
    })
  }

  public async down () {
    this.schema.table(this.tableName, (table) => {
      table.string('email', 255).notNullable()
    })
  }

and running migration print this :
× fatal error: alter table "users" add column "email" varchar(255) not null - « email » colum from « users » relation already exist

I don't want to add but update column

[iammrsea]

Try this:

table.string('email').unique().alter()
or 
table.string('email').alter().unique()

I am not it's going to work though

[Hakamate]

Thank but that not working :
× fatal error: alter table "users" add constraint "users_email_unique" unique ("email") - could not create unique index "users_email_unique"

Do i have to do something in my database or Model ?

[iammrsea]

Perhaps you can just drop the column and add it again. Something like this:

table.dropColumn('email');
table.string('email').unique().notNullable()

[Hakamate]

Yes that will work, but if I drop the column, all data will be deleted 😅

[iammrsea]

Oh my bad, didn't know you already had some important data in your database. Perhaps, you can ensure the uniqueness of the column on application level using validator. Something like this:

await request.validate({
schema: schema.create({
email: schema.string({trim:true},[
rules.email(),
rules.unique({table:'users',column:'email})
]),
...
})
})

With that, the email column will remain unique

[Hakamate]

That works but is just a validation before register. There is any way to just put a unique constraint by updating colomn ?

(Thanks for all your responses, all works :D)

[kennedyrs]

AdonisJS uses Knex behind, take a look at Knex documentation you can use this method.

If you have any email record duplicated at the database in the moment of the alter, it will not work

[Hakamate]

I saw Knex, but yes i will use it if i can't find other solutions.

Thanks !