34

I have a column: standard BOOLEAN NOT NULL

I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with plpgsql, but this seems like a sledgehammer. I would prefer something like a CHECK or UNIQUE constraint. The simpler the better.

One row must be True, they cannot all be False (so the first row inserted would have to be True).

The row will need to be updated, which means I have to wait to check constraints until updates are done, since all rows may be set False first and one row True afterwards.

There is a FK between products.tax_rate_id and tax_rate.id, but it has nothing to do with the default or standard tax rate, which is user selectable to ease creating new products..

PostgreSQL 9.5 if it matters.

Background

The table is the tax rate. One of the tax rates is the default (standard since default is a Postgres command). When a new product is added, the standard tax rate is applied to the product. If there is no standard, the database must either do a guess or all kinds of unneeded checks. The simple solution, I thought, was to make sure there is a standard.

By "default" above, I mean for the presentation layer (UI). There is a user option for changing the default tax rate. I either need to add extra checks to ensure the GUI/user does not try to set the tax_rate_id to NULL, or then just set a default tax rate.

1
  • Yes I have my answer, thanks a lot for your input, @ErwinBrandstetter. I am leaning toward a trigger for now. This is an open source project on my own time. When I actually implement it, I will mark the answer accepted I use.
    – theGtknerd
    Mar 15, 2018 at 2:59

2 Answers 2

26

Variant 1

Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it:

CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
 , standard bool DEFAULT true
 , CONSTRAINT standard_true_or_null CHECK (standard) -- that's all
 , CONSTRAINT standard_only_1_true UNIQUE (standard)
);

DEFAULT is an optional reminder that the first row entered should become the default. It's not enforcing anything. While you cannot set more than one row to standard = true, you can still set all rows NULL. There is no clean way to prevent this with only constraints in a single table. CHECK constraints do not consider other rows (without dirty tricks).

Related:

To update:

BEGIN;
UPDATE taxrate SET standard = NULL WHERE standard;
UPDATE taxrate SET standard = TRUE WHERE taxrate = 2;
COMMIT;

To allow a command like (where the constraint is only satisfied at the end of the statement):

WITH kingdead AS (
   UPDATE taxrate
   SET standard = NULL
   WHERE standard
   )
UPDATE taxrate
SET standard = TRUE
WHERE taxrate = 1;

.. the UNIQUE constraint would have to be DEFERRABLE. See:

dbfiddle here

Variant 2

Have a second table with a single row like:

Create this as superuser:

CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
);

CREATE TABLE taxrate_standard (
   taxrate int PRIMARY KEY REFERENCES taxrate
);

-- singleton!
CREATE UNIQUE INDEX taxrate_standard_singleton ON taxrate_standard ((true));

REVOKE DELETE ON TABLE taxrate_standard FROM public;  -- can't delete

INSERT INTO taxrate (taxrate) VALUES (42);
INSERT INTO taxrate_standard (taxrate) VALUES (42);

Now there is always a single row pointing to the standard (in this simple case also representing the standard rate directly). Only a superuser could break it. You might disallow that, too, with a trigger BEFORE DELETE.

dbfiddle here

Related:

You might add a VIEW to see the same as in variant 1:

CREATE VIEW taxrate_combined AS
SELECT t.*, (ts.taxrate = t.taxrate) AS standard
FROM   taxrate t
LEFT   JOIN taxrate_standard ts USING (taxrate);

In queries where all you want is the standard rate, use (only) taxrate_standard.taxrate directly.


You later added:

There is a FK between products.tax_rate_id and tax_rate.id

A poor man's implementation of variant 2 would be to just add a row to products (or any similar table) pointing to the standard tax rate; a dummy product you might call "Standard tax rate" - if your setup allows it.

The FK constraints enforces referential integrity. To complete it, enforce tax_rate_id IS NOT NULL for the row (if that's not the case for the column in general). And disallow its deletion. Both could be put into triggers. No extra table, but less elegant and not as reliable.

3
  • 2
    Highly recommend the two table approach. I'd also suggest adding an example query to that variation so the OP can see how to CROSS JOIN against standard, LEFT JOIN to the specific, and then COALESCE between the two.
    – jpmc26
    Feb 10, 2018 at 21:23
  • 2
    +1, I had the same idea about the extra table but no time to write properly an answer. About the first table and the CONSTRAINT standard_only_1_true UNIQUE (standard): I suppose the table won't be big so it doesn't matter much but since the constraint will define an index on the whole table, wouldn't a partial unique index with WHERE (standard) use less space? Feb 11, 2018 at 0:58
  • @ypercubeᵀᴹ: Yes, the index on the whole table is bigger, that's a drawback for this variant. But like you said: it's obviously a tiny table, so it hardly matters. I was aiming for the simplest standard solution with only constraints. Proof of concept. Personally, I am with jpmc26 and strongly favor variant 2. Feb 11, 2018 at 3:44
20

You can use a Filtered index

create table test
(
    id int primary key,
    foo bool
);
CREATE UNIQUE INDEX only_one_row_with_column_true_uix 
    ON test (foo) WHERE (foo);  --> where foo is true
insert into test values (1, false);
insert into test values (2, true);
insert into test values (3, false);
insert into test values (4, false);
insert into test values (5, true);
ERROR:  duplicate key value violates unique constraint "only_one_row_with_column_true_uix"
DETAIL:  Key (foo)=(t) already exists.

dbfiddle here


But as you said, first row must be true, then you could use a CHECK constraint, but even using a function you can delete first row later.

create function check_one_true(new_foo bool)
returns int as
$$
begin
    return 
    (
        select count(*) + (case new_foo when true then 1 else 0 end)
        from test 
        where foo = true
    );
end
$$
language plpgsql stable;
alter table test 
    add constraint ck_one_true check(check_one_true(foo) = 1); 
insert into test values (1, true);
insert into test values (2, false);
insert into test values (3, false);
insert into test values (4, false);
insert into test values (5, true);
ERROR:  new row for relation "test" violates check constraint "ck_one_true"
DETAIL:  Failing row contains (5, t).

select * from test;
id | foo
-: | :--
 1 | t  
 2 | f  
 3 | f  
 4 | f  
delete from test where id = 1;

dbfiddle here


You could solve it by adding a BEFORE DELETE trigger to ensure first row (foo is true) is never deleted.

create function dont_delete_foo_true()
returns trigger as
$x$
begin
    if old.foo then
        raise exception 'Can''t delete row where foo is true.';
    end if;
    return old;
end;
$x$ language plpgsql;
create trigger trg_test_delete
before delete on test
for each row 
execute procedure dont_delete_foo_true();
delete from test where id = 1;

ERROR: Can't delete row where foo is true.

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.