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

Say we have users and each user can have multiple email addresses

CREATE TABLE emails (
    user_id integer,
    email_address text,
    is_active boolean
)

Some sample rows

user_id | email_address | is_active
1       | alice@bar.com   | t
1       | alice@foo.com   | f
1       | alice@web.com   | f
2       | bob@ddd.com     | t

I want to enforce a constraint that every user has exactly one active address. How can I do this in Postgres? I could do this:

CREATE UNIQUE INDEX "user_email" ON emails(user_id) WHERE is_active=true;

Which would protect against a user having more than one active address, but wouldn't, I believe, protect against all of their addresses being set to false.

If possible I'd prefer to avoid a trigger or a pl/pgsql script, as we don't currently have any of those & it would be difficult to set up. But I would appreciate knowing "the only way to do this is with a trigger or pl/pgsql", if that is the case.

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".)

Aside: If (user_id, email) is unique (as can be assumed), you might use email directly instead of email_id ...

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;

db<>fiddle here

Old sqlfiddle

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:

• How to deal with mutually recursive inserts

• Complex foreign key constraint in SQLAlchemy

Also related:

• Enforcing constraints "two tables away"

About DEFERRABLE constraints:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

About nextval() and pg_get_serial_sequence():

• PostgreSQL SELECT primary key as "serial" or "bigserial"

• Duplicate row with Primary Key in PostgreSQL

StackExchange Database Administrators Q#112061, answer score: 25