Sql Server - Constraint - Sequelize - Allow to set column A only if column B is null and vice-versa

Question

The question I'm asking has been answered before here: Sql Server - Constraint - Allow to set column A only if column B is null and vice-versa

However, I'm not sure how to implement this in sequelize.

I noticed sequelize has a validator option you can use on it's models, and while this works during entry using the ORM, it does not add a 'true' constraint to the database since I can still insert things manually into the database. This is a problem for my use case.

validate: {
            customValidator(value) {
              if (value !== null && this.cash_amount !== null) {
                throw new Error("Percent of Equity and Cash Amount CAN NOT both have values!");
              }
            }
      }

How do I add a 'true constraint' like in the answer I linked?

The SQL way of doing what I want is this:

ALTER TABLE foo WITH CHECK ADD
    CONSTRAINT CK_Foo_reason CHECK (
        ColA IS NOT NULL AND ColB IS NULL
        OR
        ColA IS NULL AND ColB IS NOT NULL
        )

Just like the question I linked, this is my goal:

Example

if A is NULL, B can have a value
if B iS NULL, A can have a value
A and B can't have value at the same time
A and B can't BOTH be null at the same time

Extra Info:

https://sequelize.org/master/manual/validations-and-constraints.html#difference-between-validations-and-constraints

Sequelize appears to recognize the difference between it's validations which are done at the model level and not with the SQL engine and true constraints as it says on this article. Does this mean there is no way to do this to automatically create the constraints in sequelize?

score 1 · Accepted Answer · answered Jun 27 '21 at 02:27

So the only way I could find to do this was to add it using a raw SQL Query

So it looks something like this:

//@JA - SQL Enforced Constraints are NOT supported by sequelize so we are going to add them manaually here
            let sql_for_constraint = 'ALTER TABLE TradingViewAlerts DROP CONSTRAINT ck_pe_cash_null_or_not'; 
            await sequelize.query(sql_for_constraint, { type: sequelize.QueryTypes.RAW });
            let sql_for_tables = "ALTER TABLE TradingViewAlerts ADD CONSTRAINT ck_pe_cash_null_or_not CHECK (percent_of_equity IS NOT NULL and cash_amount IS NULL OR percent_of_equity IS NULL and cash_amount IS NOT NULL);";
            await sequelize.query(sql_for_tables, { type: sequelize.QueryTypes.RAW });

First you will want to drop the constraint if you added it before, then add it like so. The following code did the trick for me, even if BOTH are null it will trigger one of the constraints so I didn't need a third check.

I do this in ADDITION to the validations on the model layer for extra protection from data corruption.

You can test this by trying to add manually to the database and it will throw an error as expected now.

I feel like there 'should' be a way to do this with sequelize properly, so if anyone finds this please post!

Sql Server - Constraint - Sequelize - Allow to set column A only if column B is null and vice-versa

1 Answers1