使用 sequelize 添加 SQL 表 CHECK? [英] Add SQL table CHECK using sequelize?

问题描述

我想在我的一张表中添加此 CHECK.我在 Postgres DB 上运行的 node.js api 中使用 Sequelize.

I want to add this CHECK in one of my tables. I am using Sequelize in a node.js api running on Postgres DB.

CHECK (
    (
        CASE WHEN UserID         IS NULL THEN 0 ELSE 1 END
        + CASE WHEN TableID      IS NULL THEN 0 ELSE 1 END
        + CASE WHEN FoodID       IS NULL THEN 0 ELSE 1 END
        + CASE WHEN RestaurantID IS NULL THEN 0 ELSE 1 END
        + CASE WHEN CategoryID   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN ShipID       IS NULL THEN 0 ELSE 1 END
    )
    = 1
)

因此，我需要数据库强制检查表，其中这些列之一必须不为空，只有一列必须为空.

So, I need the database to enforce the check on the table where one of those columns must be not null, and only one, the others must be null.

有没有办法在 Sequelize 中做到这一点?

Is there a way to do that in Sequelize?

我尝试这样做:

let sql = `ALTER TABLE TestTable
ADD CONSTRAINT check_test CHECK testBool=false;`
await sequelize.query(sql, { type: sequelize.QueryTypes.RAW });

我刚刚创建了带有 testBool 列的 TestTable 只是为了测试它们是否会运行.

I just created TestTable with a testBool column just to test things out to see if they would run at all.

运行时出现此错误:

relation "testtable" does not exist

我不明白为什么名字都是小写的.在模型定义中，我什至有:

I don't understand why the name is all lowercase. In the model definition I even have the:

freezeTableName: true

我可以看到数据库中的表，我可以将行保存到它等等......所以，它就在那里.

I can see the Table in the DB, I can save rows to it etc... so, it's there.

推荐答案

从我在评论中得到的反馈:

From the feedback I got in the comments:

1. 使用 sequelize.QueryTypes.RAW

1. use the sequelize.QueryTypes.RAW

遵循正确的命名约定Postgres 命名规则

1. 运行此查询有效:

    let sql = 'ALTER TABLE test_table ADD CONSTRAINT check_test CHECK testBool=false';

    await sequelize.query(sql, { type: sequelize.QueryTypes.RAW });

这篇关于使用 sequelize 添加 SQL 表 CHECK?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！