Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table user_emails (
    user_id int not null, -- foreign key
    email text not null,
    is_confirmed boolean not null
);
Alter Table user_emails
Add Constraint address_may_only_be_associated_with_a_user_once
Unique ( user_id, email );
Create Unique Index address_may_only_be_confirmed_once
On user_emails ( email )
Where is_confirmed;
Create Unique Index user_may_have_only_one_confirmed_address
On user_emails ( user_id )
Where is_confirmed;
-- Create Unique Index user_may_have_only_one_unconfirmed_address
-- On user_emails ( user_id )
-- Where Not is_confirmed;
Alter Table user_emails
Add Constraint unconfirmed_must_not_match_confirmed
Exclude Using gist ( 
    email With =,
    Cast(is_confirmed as Int) With <>
);
Insert Into user_emails
( user_id, email, is_confirmed )
values
( 1, 'alpha@example.com', true ),
( 2, 'beta@example.com', false ),
( 3, 'beta@example.com', false )
3 rows affected

Insert Into user_emails
( user_id, email, is_confirmed )
values
( 3, 'alpha@example.com', false );
ERROR:  conflicting key value violates exclusion constraint "unconfirmed_must_not_match_confirmed"
DETAIL:  Key (email, (is_confirmed::integer))=(alpha@example.com, 0) conflicts with existing key (email, (is_confirmed::integer))=(alpha@example.com, 1).


Insert Into user_emails
( user_id, email, is_confirmed )
values
( 3, 'alpha@example.com', true );
ERROR:  duplicate key value violates unique constraint "address_may_only_be_confirmed_once"
DETAIL:  Key (email)=(alpha@example.com) already exists.


Insert Into user_emails
( user_id, email, is_confirmed )
values
( 4, 'beta@example.com', true );
ERROR:  conflicting key value violates exclusion constraint "unconfirmed_must_not_match_confirmed"
DETAIL:  Key (email, (is_confirmed::integer))=(beta@example.com, 1) conflicts with existing key (email, (is_confirmed::integer))=(beta@example.com, 0).


Insert Into user_emails
( user_id, email, is_confirmed )
values
( 1, 'beta@example.com', false );
1 rows affected

Select * From user_emails;


user_id
email
is_confirmed


1
alpha@example.com
t

2
beta@example.com
f

3
beta@example.com
f

1
beta@example.com
f
user_id	email	is_confirmed
1	alpha@example.com	t
2	beta@example.com	f
3	beta@example.com	f
1	beta@example.com	f