Return to Answer

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

• How to deal with mutually recursive inserts

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

• How to deal with mutually recursive inserts

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

• Active emails are unique, enforced by the PK constraint of email.
• Each user can have any number of active and deleted emails, but ...
• Each user can only have one primary email.
• Every email is always owned by one user and is deleted with the user.
• To soft-delete an email (without losing it and its affiliation to its user, move the row from email to email_deleted.
• The primary email of a user cannot be deleted this way, because the primary email must not be deleted.
• I designed the FK constraint users_primary_email_fkey to span (user_id, email), which seems redundant at first. But this way the primary email can only be an email that is actually owned by the same user.
  Due to the default MATCH SIMPLE behavior of FK constraints, you can still enter a user without primary email, because the FK constraint is not enforced if any of the columns is null.
  Details:
• Two-column foreign key constraint only when third column is NOT NULL

• Active emails are unique, enforced by the PK constraint of email.
• Each user can have any number of active and deleted emails, but ...
• Each user can only have one primary email.
• Every email is always owned by one user and is deleted with the user.
• To soft-delete an email (without losing it and its affiliation to its user, move the row from email to email_deleted.
• The primary email of a user cannot be deleted this way, because the primary email must not be deleted.
• I designed the FK constraint users_primary_email_fkey to span (user_id, email), which seems redundant at first. But this way the primary email can only be an email that is actually owned by the same user.
  Due to the default MATCH SIMPLE behavior of FK constraints, you can still enter a user without primary email, because the FK constraint is not enforced if any of the columns is null.
  Details:
• Two-column foreign key constraint only when third column is NOT NULL

SELECT u.*, e.emails FROM users u , LATERAL ( SELECT ARRAY ( SELECT email FROM email WHERE user_id = u.user_id ORDER BY (email <> u.email) -- sort primary email first ) AS emails ) e WHERE user_id = 123; 

You could create a VIEW with this for ease of use.

WITH upd AS ( UPDATE users u SET email = NULL FROM (SELECT user_id, email FROM users WHERE user_id = 123 FOR UPDATE) old WHERE old.user_id = u.user_id AND u.user_id = 123 RETURNING old.* ) , del AS ( DELETE FROM email USING upd WHERE email.email = upd.email ) INSERT INTO email_deleted (email, user_id) SELECT email, user_id FROM upd; 

Quick test for all of the above: SQL Fiddle.

SELECT u.*, e.emails FROM users u , LATERAL ( SELECT ARRAY ( SELECT email FROM email WHERE user_id = u.user_id ORDER BY (email <> u.email) -- sort primary email first ) AS emails ) e WHERE user_id = 1; 

You could create a VIEW with this for ease of use.
LATERAL requires Postgres 9.3. use a correlated subquery in pg 9.2:

SELECT *, ARRAY ( SELECT email FROM email WHERE user_id = u.user_id ORDER BY (email <> u.email) -- sort primary email first ) AS emails FROM users u WHERE user_id = 1; 

WITH upd AS ( UPDATE users u SET email = NULL FROM (SELECT user_id, email FROM users WHERE user_id = 123 FOR UPDATE) old WHERE old.user_id = u.user_id AND u.user_id = 1 RETURNING old.* ) , del AS ( DELETE FROM email USING upd WHERE email.email = upd.email ) INSERT INTO email_deleted (email, user_id) SELECT email, user_id FROM upd; 

Quick test for all of the above: SQL Fiddle.