CODEWITHSUNDEEP
node.jsknex.js
Sérgio Avilla
Sérgio Avilla

Reputation: 27

Why this foreign key is not working for delete action in knex?

I have two tables, addresses and users, each user has an address, there is a column 'address_id' in table users that references a row in addresses table. But, when I delete a user, the address row is dont removed. Why?

table_users:

exports.up = function(knex) {
    return knex.schema.createTable('users', function(table) {
        table.increments('id')
        table.text('name')
        table.text('cpf').unique()
        table.text('email').unique()
        table.text('pass')
        table.text('phone')
        table.timestamp('last_access').defaultTo(knex.fn.now())
        table.timestamp('created_at').defaultTo(knex.fn.now())
        table.timestamp('deleted_at').defaultTo(null)
        table.integer('address_id').unsigned()
        table.foreign('address_id').references('addresses.id').onDelete('CASCADE')
    })
};

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

table_addresses:

exports.up = function(knex) {
    return knex.schema.createTable('addresses', function(table) {
        table.increments('id')
        table.text('street')
        table.text('number')
        table.text('complement')
        table.text('city')
        table.text('state')
        table.text('country')
        table.timestamp('last_access').defaultTo(knex.fn.now())
        table.timestamp('created_at').defaultTo(knex.fn.now())
        table.timestamp('deleted_at').defaultTo(null)
    })
};

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

del function:

const del = (req, res, next) => { // TODO: colocar transaction aqui também
        knex('users').where({
            id: req.userData.id
        }).first().del().then(r => {

            if (!r) {
                throw new CompleteError('Falha ao deletar usuário.', 400)
            }

            res.status(200).json({
                sucess: true,
                data: {
                    message: 'Usuário deletado com sucesso.'
                }
            })
        }).catch(e => {
            return next(e)
        })
    }

Upvotes: 0

Views: 798

Answers (1)

felixmosh
felixmosh

Reputation: 35493

With your current definition, the reference works backwards, that means that when ever you delete an address, it will delete the referenced user.

If you want that whenever you delete a user it will delete the address, you need to add a reference in addresses table to a user with onDelete('cascade').

Upvotes: 2

Related Questions