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

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

sqlpostgresqlconstraints


Solution 1:[1]

You can do

Check (flag_1 or flag_2)

Solution 2:[2]

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/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Mostaf Chronic

Related Questions