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

Question

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.

Answer 1

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

Answer 2

If you can add a column to the table, the following scheme would almost¹ work:

CREATE TABLE emails 
(
    UserID integer NOT NULL,
    EmailAddress varchar(254) NOT NULL,
    IsActive boolean NOT NULL,

    -- New column
    ActiveAddress varchar(254) NOT NULL,

    -- Obvious PK
    CONSTRAINT PK_emails_UserID_EmailAddress
        PRIMARY KEY (UserID, EmailAddress),

    -- Validate that the active address row exists
    CONSTRAINT FK_emails_ActiveAddressExists
        FOREIGN KEY (UserID, ActiveAddress)
        REFERENCES emails (UserID, EmailAddress),

    -- Validate the IsActive value makes sense    
    CONSTRAINT CK_emails_Validate_IsActive
    CHECK 
    (
        (IsActive = true AND EmailAddress = ActiveAddress)
        OR
        (IsActive = false AND EmailAddress <> ActiveAddress)
    )
);

-- Enforce maximum of one active address per user
CREATE UNIQUE INDEX UQ_emails_One_IsActive_True_PerUser
ON emails (UserID, IsActive)
WHERE IsActive = true;

Test SQLFiddle

_{Translated from my native SQL Server, with help from a_horse_with_no_name}

As ypercube mentioned in a comment, you could even go further:

• Drop the boolean column; and
• Create the UNIQUE INDEX ON emails (UserID) WHERE (EmailAddress = ActiveAddress)

The effect is the same, but it is arguably simpler and neater.

---

¹ The problem is that the existing constraints only ensure that a row referred to as 'active' by another row exists, not that it is also actually active. I don't know Postgres well enough to implement the extra constraint myself (at least not right now), but in SQL Server, it could be done thusly:

CREATE TABLE Emails 
(
    EmailID integer NOT NULL UNIQUE,
    UserID integer NOT NULL,
    EmailAddress varchar(254) NOT NULL,
    IsActive bit NOT NULL,

    -- New columns
    ActiveEmailID integer NOT NULL,
    ActiveIsActive AS CONVERT(bit, 'true') PERSISTED,

    -- Obvious PK
    CONSTRAINT PK_emails_UserID_EmailAddress
        PRIMARY KEY (UserID, EmailID),

    CONSTRAINT UQ_emails_UserID_EmailAddress_IsActive
        UNIQUE (UserID, EmailID, IsActive),

    -- Validate that the active address exists and is active
    CONSTRAINT FK_emails_ActiveAddressExists_And_IsActive
        FOREIGN KEY (UserID, ActiveEmailID, ActiveIsActive)
        REFERENCES emails (UserID, EmailID, IsActive),

    -- Validate the IsActive value makes sense    
    CONSTRAINT CK_emails_Validate_IsActive
    CHECK 
    (
        (IsActive = 'true' AND EmailID = ActiveEmailID)
        OR
        (IsActive = 'false' AND EmailID <> ActiveEmailID)
    )
);

-- Enforce maximum of one active address per user
CREATE UNIQUE INDEX UQ_emails_One_IsActive_PerUser
ON emails (UserID, IsActive)
WHERE IsActive = 'true';

This effort improves a little on the original by using a surrogate rather than duplicating the full email address.

Answer 3

The only way to do either of these without schema changes is with a PL/PgSQL trigger.

For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd add a deferrable FK from user to address on a column active_address_id, instead of using an active flag on address.