1

I am using knex.js. I want to know how to add & drop default () to colmun later.

The column has already been created.

knex.schema.alterTable('users', function(t) {
    t.string('email', 30).notNullable()
})

The add & drop of unique found. https://knexjs.org/#Schema-unique https://knexjs.org/#Schema-dropUnique

ADD:

   knex.schema.alterTable('users', function(t) {
      t.unique('email') 
   })

DROP:

   knex.schema.alterTable('users', function(t) {
      t.dropUnique('email') 
   })

but, the default add & drop method is not found

I tried this.

knex.schema.alterTable('users', function(t) {
 t.dropUnique('email').defaultTo('masao@gmail.com')
})

I got this error message.

ER_DUP_FIELDNAME: Duplicate column name 'email'

Is there anyone who knows how?

kuniatsu
  • 23
  • 1
  • 6

2 Answers2

6

This should do it:

knex.schema.alterTable('users', function(t) {
  t.string('email', 30).notNullable().defaultTo('masao@gmail.com').alter();
});

.alter() in the end of query builder tells knex to create alter table declaration for the column instead of normal column add (in many databases altering like that is not trivial at all, but this works for basic cases and changing type / default and not nullable at least).

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
0

I'm using MSSQL.

My problem was when I tried to alter a column (which has a default constraint) from type boolean to type int. As result:

Msg 5074, Level 16, State 1, Line 35
The object 'DF__table*__column*__###' is dependent on column 'entangled'. 
    
    Msg 4922, Level 16, State 9, Line 35
ALTER TABLE ALTER COLUMN entangled failed because one or more objects access this column.

So I did a lot searching and found out that I need to drop the default constraint before I alter the data type. But the fact is that knex does not support dropping default constraint. So I went for raw() SQL.

Notice that the default constraint name should be assumed to be unknown, because when you run the migration on different server or local, the constraint names will change. So the idea was to get the default constraint name first and drop it. According to this post by Philip Kelley, you will find methods to do the trick.

My answer is not a correct one for this question. But I hope this post can help those fellows who encountered the same issue like I was.

CharlieIsAwesome
  • 73
  • 5