SQL Constraint don't allow both flags to be false-CodePudding

I have the following table:

CREATE TABLE test(
   flag_1 boolean not null default true,
   flag_2 boolean not null default false
);

I want a constraint that doesn't allow both flags to be false. At least one of them needs to be true.

Example:

INSERT INTO test VALUES(true, true);   // Should work
INSERT INTO test VALUES(true, false);  // Should work
INSERT INTO test VALUES(false, true);  // Should work
INSERT INTO test VALUES(false, false); // Should fail

I came up with the following:

ALTER TABLE test
ADD CONSTRAINT allow_only_one_false
CHECK 
(
    ( CASE WHEN flag_1 is false and flag_2 is false then 0 ELSE 1 END
    ) = 1
);

It does work but no sure if there is a simpler way of achieving the same.

Database Fiddle:

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=78885662e4aee8bfee01c429f86f28c5

Thanks

CodePudding user response：

I think you should use the trigger after the update to make sure both of your fields are incorrect.

[Use Trigger][1]https://www.educba.com/sql-after-update-trigger/

CodePudding user response：

You can do

Check (flag_1 or flag_2)