如何使用Knex、迁移文件与PostgreSQL解决CRUD及外键问题

核心问题 #

• 删除父表关联行时，当前用.onDelete('SET NULL')修复了报错，但不希望子表出现NULL值
• 用户新增/更新时输入不存在的外键值会报错，尝试.onUpdate()无效

迁移文件代码 #

agents表 #

exports.up = function(knex) {
    return knex.schema.createTable('agents', table =>{
        table.increments()
        table.string('name')
    })
};
 
exports.down = function(knex) {
    return knex.schema
        .dropTable('agents')
};

listings表 #

exports.up = function(knex) {
    return knex.schema.createTable('listings', table =>{
        table.increments()
        table.string('address')
        table.integer('agent_id')
        table.foreign('agent_id').references('agents.id').onDelete('SET NULL')
    })
};

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

buyers表 #

exports.up = function(knex) {
    return knex.schema.createTable('buyers', table =>{
        table.increments()
        table.string('name')
        table.integer('listing_id')
        table.foreign('listing_id').references('listings.id').onDelete('SET NULL')
    })
};

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

路由代码 #

//GET all
router.get('/', (req, res) => {
    knex('buyers')
        .orderBy('id')
        .then(buyers => {
            res.status(200).json(buyers)
        })
        .catch(err => res.status(500).json(err)) 
})

router.post('/', (req, res) => {
    knex('buyers')
        .insert(req.body)
        .returning('*')
        .then(data => {
            if (req.body.name && req.body.listing_id){
                res.status(201).json(data)
            }else{
                res.status(400).send("This buyer did not post")
            }
        }).catch(error => {
            res.status(500).send(error.message)
        });
})

router.get('/:id', (req, res) => {
    knex('buyers')
        .select('name', 'listing_id', 'address', 'agent_id')
        .where({'listing_id': req.params.id})
        .join('listings', 'buyers.listing_id', 'listings.id')
        .then(data => {
            res.json(data)
        })
        .catch(err => res.status(500).json(err.message));
})

router.put('/:id', (req, res) => {
    if(req.params.id && req.body.name && req.body.listing_id){
    knex('buyers')
        .where({id: req.params.id})
        .update({name: req.body.name})
        .update({listing_id: req.body.listing_id})
        .then(res.status(200).json({message: "buyer updated"}))
        .catch(err => res.status(500).json(err))
    }else{
        res.status(400).json({error: "buyer could not be updated"})
    }
})

router.delete('/:id', (req, res) =>{
    if(req.params.id){
        knex('buyers')
            .where({id: req.params.id})
            .join('listings', 'buyers.listing_id', 'listings.id')
            .del()
            .then(res.status(200).json({message: "buyer deleted"}))
            .catch(err => res.status(500).json(err))
    }else{
        res.status(400).json({error: "No buyer id found"})
    }
})

module.exports = router;

解决方案 #

1. 解决删除关联行时的NULL值问题 #

.onDelete('SET NULL')会让子表外键字段变为NULL，要避免这个，有两种业务适配方案：

• 级联删除（CASCADE）：删除父表行时，自动删除所有关联的子表行。适合“父表行删除后子表行无存在意义”的场景，修改迁移文件的外键配置：

  // listings表的agent_id外键
  table.integer('agent_id').notNullable() // 先加非空约束，避免NULL
  table.foreign('agent_id').references('agents.id').onDelete('CASCADE')
  
  // buyers表的listing_id外键
  table.integer('listing_id').notNullable()
  table.foreign('listing_id').references('listings.id').onDelete('CASCADE')
  

• 阻止删除（RESTRICT/NO ACTION）：如果子表存在关联行，直接阻止父表行删除。PostgreSQL默认外键行为是NO ACTION，和RESTRICT效果一致（立即检查约束），修改为：

  table.integer('agent_id').notNullable()
  table.foreign('agent_id').references('agents.id').onDelete('RESTRICT')
  

2. 解决外键不存在的报错问题 #

.onUpdate()是用来定义父表主键更新时的子表行为（比如父表id改变时，子表外键同步更新），和“校验外键值是否存在”无关。解决方法如下：

数据库层面（推荐）

PostgreSQL的外键约束会自动校验输入的外键值是否存在，报错时错误码为23503，只需在路由的catch块中捕获该错误，返回友好提示：

// 以POST路由为例
router.post('/', (req, res) => {
    knex('buyers')
        .insert(req.body)
        .returning('*')
        .then(data => {
            res.status(201).json(data)
        }).catch(error => {
            if (error.code === '23503') {
                return res.status(400).json({ error: '关联的房源ID不存在' });
            }
            res.status(500).json({ error: error.message });
        });
})

同时给外键字段加.notNullable()约束，从根源避免NULL值。

应用层面（可选）

在执行新增/更新前，先查询父表是否存在对应的id：

// POST路由示例
router.post('/', (req, res) => {
    const { name, listing_id } = req.body;
    if (!name || !listing_id) {
        return res.status(400).json({ error: '缺少必要字段' });
    }
    // 先检查listing是否存在
    knex('listings').where({ id: listing_id })
        .then(listing => {
            if (!listing.length) {
                return res.status(400).json({ error: '关联的房源ID不存在' });
            }
            // 执行插入
            return knex('buyers').insert({ name, listing_id }).returning('*');
        })
        .then(data => res.status(201).json(data))
        .catch(err => res.status(500).json({ error: err.message }));
})

路由代码优化点 #

• PUT路由中的两次.update()可以合并为一个对象：.update({ name: req.body.name, listing_id: req.body.listing_id })
• DELETE路由无需join listings表，直接删除buyers即可，外键约束会自动处理关联逻辑（如果用CASCADE）

内容的提问来源于stack exchange，提问作者jSvSL