How to create tables that refer to each other?

Question

exports.up = async function (knex, Promise) {
    await knex.schema.createTable('courses', function (table) {
        table.primary('id');
        table.string('name').notNullable();
        table.text('description').notNullable();
        table.integer('id').references('files.course_id').notNullable();
        table.timestamps(true, true);
    });
};

exports.up = async function (knex, Promise) {
    await knex.schema.createTable('files', function (table) {
        table.increments().primary();
        table.string('name').notNullable();
        table.string('path').notNullable();
        table.text('description').notNullable();
        table.integer('course_id').references('courses.id').notNullable();
        table.timestamps(true, true);
    });
};

alter table "courses" add constraint "courses_id_foreign" foreign key ("id") references "files" ("course_id") - relation "files" does not exist

I want to create two tables that refer to one another. How to do it right

Answer 1

You shouldn't do two way linking between tables. That is usually sign of a bad database design.

In this case I suppose you should have many to one relation where course can have multiple files.

In that case files table should have the foreign key pointing to courses.

exports.up = async function (knex, Promise) {

  await knex.schema.createTable('courses', function (table) {
    table.increments('id'); // increments is automatically set to be primary key
  });

  await knex.schema.createTable('files', function (table) {
    table.increments('id'); 
    table.integer('course_id').unsigned().notNullable()
      .references('id').inTable('courses');
  });

}

If you want that each course can have just one file, then you should have foreign key pointing from courses to files.

exports.up = async function (knex, Promise) {

  await knex.schema.createTable('files', function (table) {
    table.increments('id');
  });

  await knex.schema.createTable('courses', function (table) {
    table.increments('id');
    table.integer('file_id').unsigned().notNullable()
      .references('id').inTable('files');
  });
}

However if you want to do it you need to first create one table without foreign key and then second table and after that you can add foreign key pointing to the second table.

This should do more or less that

exports.up = async function (knex, Promise) {

  await knex.schema.createTable('courses', function (table) {
    table.increments('id');
  });

  await knex.schema.createTable('files', function (table) {
    table.increments('id'); 
    table.integer('course_id').unsigned().notNullable()
      .references('id').inTable('courses');
  });

  await knex.schema.table('courses', function (table) {
    table.integer('file_id').unsigned().notNullable()
      .references('id').inTable('files');
  });

}

Also there are other errors in the code above, like you are trying to set primary key of the course table to refer files table's course_id which is pointing to primary key of the courses table...

Disclaimer: I didn't even try to run the code I wrote, but it should give the basic idea.