Node.js Knex and mySQL - ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails

Question

I am trying to create simple web app to receive dog adoption applications.

I succesfully run migrations and seeds and by doing so created these two tables:

The problem is that when I try to create new application using GUI, I get the below error:

{"response":"Error in database ForeignKeyViolationError: insert into applications (doggo_name, email, name, phone) values ('Coco', '[email protected]', 'Sam Do', '+12345667') - ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (dog_adoption.applications, CONSTRAINT applications_doggo_id_foreign FOREIGN KEY (doggo_id) REFERENCES doggos (id))"}

This is second day I am trying to figure out what is wrong. Please see my code:

MIGRATION FILE:

exports.up = function(knex) {

    return knex.schema
        .createTable('doggos', (table) => {
            table.increments('id').notNullable();
            table.string('doggo').notNullable();
            table.integer('age').notNullable();
            table.string('breed').notNullable();
            table.string('picture').notNullable();
        })
        .createTable('applications', (table) => {
            table.increments('id').notNullable();
            table.string('name').notNullable();
            table.string('email').notNullable();
            table.integer('phone').notNullable();
            table.string('doggo_name').notNullable();
            table.integer('doggo_id').unsigned().notNullable();
            table.foreign('doggo_id').references('doggos.id');
            table.dateTime('updated_at').defaultTo(knex.raw('NULL ON UPDATE CURRENT_TIMESTAMP'));
            table.dateTime('created_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP'));
        });
};

APPLICATION seed:

exports.seed = function(knex) {
 
    return knex('doggos').select().then(doggos => {
        return knex('applications').insert([
  
      { name: "xxxxxxxxx", email: "[email protected]", phone: 79187877, doggo_name: 'Coco', doggo_id: doggos.find(doggo => doggo.doggo === 'Coco').id},
      { name: "xxxxxxxxxxxxx", email: "[email protected]", phone: 51393129, doggo_name: 'Tyson', doggo_id: doggos.find(doggo => doggo.doggo === 'Tyson').id},
      { name: "xxxxxxxxxxxxx", email: "[email protected]", phone: 12345678, doggo_name: 'Nicky', doggo_id: doggos.find(doggo => doggo.doggo === 'Nicky').id}
    ]);
});

};

HTML FORM:

      <form action="/apply" method="POST">
         <div class="application-container">
            <label for="name">What is your name?</label>
            <input type="text" placeholder="Your name" name="name" required>   
            <label for="email">E-mail address</label>
            <input type="text" placeholder="e-mail" name="email" required>
            <label for="phone">Phone number</label>
            <input type="text" placeholder="phone" name="phone" required>
            <label for="doggo_name">Name of dog you are interested with</label>
            <input type="text" placeholder="Name of dog you are interested with" name="doggo_name" required>
            <button class="btn btn-primary" type="submit">Submit</button>
            <button class="btn btn-primary" onclick="window.location.href='/'">Cancel</button>
         </div>
      </form>
   </body>

ROUTE:

router.post('/apply', async (req,res) => {
    const { name, email, phone, doggo_name } = req.body;
    console.log(name, email, phone, doggo_name);

    try {
   
        const submittedApplication =  await Application.query().insert({
        name, 
        email,
        phone,
        doggo_name,
        // how to pass doggo_id to the database?

    });
    return res.send({ response: `Succesfully applied for adoption. Please wait patiently for our response!`});
    
    } catch (error) {
    return res.send({ response: "Error in database " + error });
    }
});

I would really appreciate if somebody could look at it with a fresh eye nd give me a hand with data persistence to my 'applications' table.

Answer 1

You make the doggo_id not nullable, so it will get 0 as default for all existing rows instead of NULL.

But then you also set it as foreign key to doggos.id. The foreign key constraint immediately fails on all rows since they would now all reference the doggo with ID 0 which presumably doesn't exist.

You can solve that problem by making it nullable (remove notNullable() from doggo_id), which works because NULL means "not referencing anything at the moment" (as opposed to "referencing the doggo with ID zero"), or by setting a default of an ID that belongs to an actually existing doggo and not zero, if that makes sense in your use case.