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:
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', '[email protected]') -- new users with *1* active email
, ('usr2', '[email protected]')
, ('usr3', '[email protected]')
)
, 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, '[email protected]')
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:
Also related:
About DEFERRABLE
constraints:
About nextval()
and pg_get_serial_sequence()
: