Are nullable foreign keys possible with objectionjs/knex?

Question

Maybe this is a simple fix, but I can't seem to figure out what I'm doing wrong here.

I have a table that lists all the states Model:

static get jsonSchema() {
    return {
      type: 'object',

      properties: {
        id: { type: 'integer' },
        name: { type: 'string', minLength: 1, maxLength: 100 },
      },
    }
  }

  static get relationMappings() {
    return {
      users: {
        relation: Model.HasManyRelation,
        modelClass: User,
        join: {
          from: `${tableNames.state}.id`,
          to: `${tableNames.user}.state_id`,
        },
      },
    }

Migration:

await knex.schema.createTable(tableNames.state, (table) => {
    table.increments().primary().notNullable()
    table.string('name', 100).notNullable()

---

User table model:

static get jsonSchema() {
    return {
      type: 'object',

      properties: {
        id: { type: 'integer' },
        first_name: { type: 'string', minLength: 1, maxLength: 100 },
        last_name: { type: 'string', minLength: 1, maxLength: 100 },
        state_id: { type: 'integer', default: null },
      },
    }
  }

  static get relationMappings() {
    return {
      state: {
        relation: Model.BelongsToOneRelation,
        modelClass: State,
        join: {
          from: `${tableNames.user}.state_id`,
          to: `${tableNames.state}.id`,
        },
      }
    }
  }

User table migration:

await knex.schema
    .createTable(tableNames.user, (table) => {
      table.increments().primary().notNullable()
      table.string('first_name', 100).notNullable()
      table.string('last_name', 100).notNullable()
      table.integer('state_id').unsigned()

      table
        .foreign('state_id')
        .references('id')
        .inTable(tableNames.state)
        .onDelete('SET NULL')
    })

---

Now the issue: I want the state_id column to be nullable, as in not every user will have a state assigned to them. But when I try inserting a user with no state_id, I get this: insert or update on table \"user\" violates foreign key constraint \"user_state_id_foreign\".

Answer 1

two things you are doing wrong

1. in your json schema define your column as state_id: {type: ['integer', 'null']}
2. in your user migrations make table.integer('state_id').unsigned().nullable()