Add notNullable to foreignKeys without having trouble in seeding

Hello everyone!

First of all, thank you so much for taking the time to read me.

I’ve implemented the schema of my database into migrations in AdonisJS following the documentation and some tips from this forum. This is what I have (it’s an example of what I have cause I cannot share the original field names):

I have two migrations which represent two tables related to each other.

//First migration's up method

up () {
    this.create('activity_types', (table) => {
      table.increments()
      table.string('name').unique()
      table.string('description').nullable()
      table.timestamps()
    })
  }
//Second migration's up method
up () {
    this.create('activities', (table) => {
      table.increments()
      table.string('name')
      table.string('description')
      table.integer('score')
      table.integer('activity_type_id').unsigned()
      table.foreign('activity_type_id').references('activity_types.id').onDelete('cascade')
      table.timestamps()
    })
  }

So, what I want to achieve is to set the field activity_type_id to not Nullable. This is because if I leave the code as it is above in the database it allows me to null the value of activity_type_id.
A problem I’m facing is that if I use the .notNullable() function as showing in the following code:

up () {
    this.create('activities', (table) => {
      table.increments()
      table.string('name')
      table.string('description')
      table.integer('score')
      table.integer('activity_type_id').unsigned().notNullable()
      table.foreign('activity_type_id').references('activity_types.id').onDelete('cascade')
      table.timestamps()
    })
  }

The problem I face occurs when I’m seeding. I coded the factories and seeders needed to seed my database. When I run adonis seed, it shows me an error message arguing that activity_type_id doesn’t have a default value.

The error I get is the following one:

code: 'ER_NO_DEFAULT_FOR_FIELD',
  errno: 1364,
  sqlMessage: 'Field \'activity_type_id\' doesn\'t have a default value',
  sqlState: 'HY000',

If I remove the .notNullable() method from the migration code, everything works perfectly. However, in the Database, the value is set as nullable and that’s not what I want.

Note: In Laravel, it automatically sets the value as not nullable without the need to add a notNullable() function. So, am I doing something wrong? What is the best approach to achieve what I want?

Thank you so much!

I’m going to add a visual summary of what I explained before with images from my database.

Picture #1 - Table structure of activities, with no notNullable() method in migration:

30%20PM

Migration and seeding are successful.

Picture #2 - Table structure of activities, with notNullable() method in migration:

40%20PM

Seeding is not successful.
However, the table structure from this picture is what I expect.

Try to add .notNullable().defaultTo(null)

Nope, now I got the same error message but when migrating.
It seems like it’s invalid to add null as default value to a not-nullable field.

It’s the constraint enforces from sql. You cannot add null to notnull column.
Try default(-1) or any value you can think of.

  1. You would need to add nonNullable if you are trying to enforce the rule at at this layer.

  2. What is your seeder code?

Did you try ActivityType.createMany(activities) or equivalent ? This should auto-populate the activity id and not have a problem.

You could also use make() and save() instead of create() when you need more control.

Finally if nothing else works, I would look at custom data (reference) to populate activity type, create object with specific activity types and pass that object for the factory.

Hello prashanth1k, thank you so much for responding me.

This is an excerpt of my seeding code related to this issue.

const activityType_1 = await Factory.model('App/Models/ActivityType').create()

const activity_1 = await Factory.model('App/Models/Activity').make()
const activity_2 = await Factory.model('App/Models/Activity').make()
const activity_3 = await Factory.model('App/Models/Activity').make()

await activityType_1.activities().save(activity_1)
await activityType_1.activities().save(activity_2)
await activityType_1.activities().save(activity_3)

So based on what you told me, the approach I should follow is something like the following one?

//Instead of ending the activityType factory with .create() ending it with .make() ?
const activityType_1 = await Factory.model('App/Models/ActivityType').make()

Hello @hecsifloz, yes indeed. I thought make/save will work, but your problem seems something else. Please do try directly with the child object as outlined by you.

You could also use custom data to pass the parent id along (https://adonisjs.com/docs/4.1/seeds-and-factories#_custom_data). I have not used that earlier, unfortunately and don’t know how it behaves :slight_smile:

1 Like