Postgresql – Constraint – one boolean row is true, all other rows false

constraintpostgresqlpostgresql-9.5referential-integrity

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.

Best Answer

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) -- yes, that's the whole constraint
 , 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).

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:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

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
);

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

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

Constraint to enforce "at least one" or "exactly one" in a database

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.

Related Solutions

Postgresql – How to enforce an “exactly-one-per-group” constraint

The problem is similar to this one: How to have a one-to-many relationship with a privileged child?

The "at most one per group" part of the constraint can be solved with a partial index:

CREATE UNIQUE INDEX is_FavoriteChild
  ON x (yid)
  WHERE is_principal  ;

Another way to solve the problem is by removing the is_principal column and add a 3rd table. This doesn't solve the "exactly one" either:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y (yid),
    UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
    xid INTEGER NOT NULL,
    yid INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (yid, xid) REFERENCES x (yid, xid) 
);

If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).

For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.

(Editing the answer for the additional detail that not all values of y.yid have to appear in table x. One table is added):

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
    yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
    --- **no other columns**
    principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y_x (yid)
                         DEFERRABLE INITIALLY DEFERRED,
    UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
   FOREIGN KEY (yid, principal_xid)
   REFERENCES x (yid, xid)
   DEFERRABLE INITIALLY DEFERRED;

Postgresql – Constraint to enforce “at least one” or “exactly one” in a database

You don't need triggers or PL/pgSQL at all.
You don't even need DEFERRABLE constraints.
And you don't need to store any information redundantly.

Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, but using data-modifying CTEs we don't even need that.

This enforces exactly one active email per user at all times:

CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text NOT NULL
, email_id int NOT NULL  -- FK to active email, constraint added below
);

CREATE TABLE email (
  email_id serial PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE 
, email    text NOT NULL
, CONSTRAINT email_fk_uni UNIQUE(user_id, email_id)  -- for FK constraint below
);

ALTER TABLE users ADD CONSTRAINT active_email_fkey
FOREIGN KEY (user_id, email_id) REFERENCES email(user_id, email_id);

Remove the NOT NULL constraint from users.email_id to make it "at most one active email". (You can still store multiple emails per user, but none of them is "active".)

You can make active_email_fkey DEFERRABLE to allow more leeway (insert user and email in separate commands of the same transaction), but that's not necessary.

I put user_id first in the UNIQUE constraint email_fk_uni to optimize index coverage. Details:

Is a composite index also good for queries on the first field?

Optional view:

CREATE VIEW user_with_active_email AS
SELECT * FROM users JOIN email USING (user_id, email_id);

Here's how you insert new users with an active email (as required):

WITH new_data(username, email) AS (
   VALUES
      ('usr1', 'abc@d.com')   -- new users with *1* active email
    , ('usr2', 'def3@d.com')
    , ('usr3', 'ghi1@d.com')
   )
, u AS (
   INSERT INTO users(username, email_id)
   SELECT n.username, nextval('email_email_id_seq'::regclass)
   FROM   new_data n
   RETURNING *
   )
INSERT INTO email(email_id, user_id, email)
SELECT u.email_id, u.user_id, n.email
FROM   u
JOIN   new_data n USING (username);

The specific difficulty is that we have neither user_id nor email_id to begin with. Both are serial numbers provided from the respective SEQUENCE. It can't be solved with a single RETURNING clause (another chicken-and-egg problem). The solution is nextval() as explained in detail in the linked answer below.

If you don't know the name of the attached sequence for the serial column email.email_id you can replace:

nextval('email_email_id_seq'::regclass)

with

nextval(pg_get_serial_sequence('email', 'email_id'))

Here's how you add a new "active" email:

WITH e AS (
   INSERT INTO email (user_id, email)
   VALUES  (3, 'new_active@d.com')
   RETURNING *
   )
UPDATE users u
SET    email_id = e.email_id
FROM   e
WHERE  u.user_id = e.user_id;

SQL Fiddle.

You might encapsulate the SQL commands in server-side functions if some simple-minded ORM isn't smart enough to cope with this.

Closely related, with ample explanation:

Also related: