Can't apply the knex change to create multi ref table when SQL_REQUIRE_PRIMARY_KEY is enabled

Wappler General How To
#1
  1. I created a multiref table using the ui (right click, New Multi Reference)
  2. This worked fine in my local dev environment (docker)
  3. When switching target to the live database (digital ocean managed db) and I try to apply the change, I get the following error: afbeelding

This is the knex code:


exports.up = function(knex) {
  return knex.schema
    .createTable('product_linked_courses', async function (table) {
      table.integer('product_id').unsigned();
      table.foreign('product_id').references('id').inTable('products').onUpdate('CASCADE').onDelete('CASCADE');
      table.integer('course_id').unsigned();
      table.foreign('course_id').references('id').inTable('courses').onUpdate('CASCADE').onDelete('CASCADE');
    })
};

exports.down = function(knex) {
  return knex.schema
    .dropTable('product_linked_courses')
};

If I look at knex code from an earlier made table (not multiref) it has table.increments('id') which I guess is the thing that’s missing?
afbeelding

#2

Well actually the Multi Reference table (junction Table) doesn’t need a primary key as it serves only as a reference to the other two tables.

However some databases servers setups (like the one from Digital Ocean) as the global option " SQL_REQUIRE_PRIMARY_KEY" enabled by default - and this generates the error.

You will be better off to disable it, see:

#3

Damn 1 min too late, I just added this increment…

Will it mess with the subtable queries/inserts/updates in any way by having a primary key incrementing integer?

To be clear, I mean this: afbeelding

#4

The option doesn’t effect your queries in any way. It is just a save check that you add a primary key (or incremental) to each table.

#5

Okay thanks!

Out of curiousity, can you link me to some resource / explain me why it’s better to disable it?
The author of the error clearly believs that is can cause performance problems

#6

I think it’s because of indexing, it’s faster to lookup a row by its index. This means you should index one or multiple columns you currently have (it seems Wappler is not doing so atm?)