2

I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users).

However, each user can also have additional emails.

In any case, an email address must be unique and I'd like to enforce this on the database level. For the primary email address that's easy. I can simply add a UNIQUE index with a WHERE not is_deleted restriction so a deleted user's email can be re-used.

However, for the secondary emails that's more tricky.

  • If I stored them in the same table in an array field I'd probably not only lose the ability to index them (we have >50k users and need to be able to search by email, so that's a no-go) but as far as I know I also cannot put an UNIQUE index/constraint which goes into the arrays.
  • If I use a separate table (which is obviously cleaner), I would have to replicate the is_deleted flag from users to that table, which is also quite ugly, but would allow me to simply use the UNIQUE index with WHERE not is_deleted.

Is there any better solution to achieve what I'm trying to do?

  • You could add a more complex conditional constraint, e.g. CREATE UNIQUE INDEX unique_email ON emails (email_address) WHERE user_id NOT IN (SELECT user_id FROM users WHERE is_deleted = true) (untested). Or perhaps use a trigger and just duplicate the is_deleted flag. – dartonw Mar 20 '15 at 19:01
  • I'm using the SQLAlchemy ORM so I'm not sure how much it likes triggers changing things it doesn't know about. Anyway, I'll probably do replicate that flag. That way I can even keep track of users' past emails (i.e. also soft-delete emails when a user removes them). – ThiefMaster Mar 20 '15 at 19:38
  • Obviously, you need to provide your current table definition (\d users in psql) and your version of Postgres with this question. – Erwin Brandstetter Mar 21 '15 at 18:38
  • 2
    @dartonw you can't have subqueries like that in a constraint. – ypercubeᵀᴹ Mar 21 '15 at 23:39
  • @ErwinBrandstetter: Postgres 9.2. I don't think the exact table definition is really necessary. Besides that fact that it can still change depending on the options the question is kind of generic... – ThiefMaster Mar 22 '15 at 0:15
3

To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails:

CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text UNIQUE NOT NULL
, email    text UNIQUE -- FK added below  -- can also be NOT NULL
);

CREATE TABLE email (
  email    text PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
, UNIQUE (user_id, email)  -- seems redundant, but required for FK
);

ALTER TABLE users ADD CONSTRAINT users_primary_email_fkey
FOREIGN KEY (user_id, email) REFERENCES email (user_id, email);

CREATE TABLE email_deleted (
  email_id serial PRIMARY KEY
, email    text NOT NULL  -- not necessarily unique
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
);

This way:

  • 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:

The UNIQUE constraint on users.email is redundant for this solution, but it may be useful for other reasons. The automatically created index should come in handy (for instance for the last query in this answer).

The only thing that's not enforced this way is that every user has a primary email. You can do this, too. Add NOT NULL constraint to users.email

UNIQUE (user_id, email) is required for the FK constraint:

You have doubtless spotted the circular reference in the above model. Contrary to what one might expect, this just works.

As long as users.email can be NULL, it's trivial:

  1. INSERT user without email.
  2. INSERT email referencing the owning user_id.
  3. UPDATE user to set it's primary email if applicable.

It even works with users.email set to NOT NULL. You have to insert user and email at the same time though:

WITH u AS (
   INSERT INTO users(username, email)
   VALUES ('user_foo', 'foo@mail.com')
   RETURNING email, user_id
   )
INSERT INTO email (email, user_id)
SELECT email, user_id
FROM   u;

IMMEDIATE FK constraints (the default) are checked at the end of each statement. The above is one statement. That's why it works where two separate statements would fail. Detailed explanation:

To get all emails of a user as array, with the primary email first:

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;

To soft-delete an email:

WITH del AS (
   DELETE FROM email
   WHERE  email = 'spam@mail.com'
   RETURNING email, user_id
   )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM del;

To soft-delete the primary email of a given user:

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;

Details:

Quick test for all of the above: SQL Fiddle.

  • Nice use of wCTEs in those examples! I was tempted to mention them in my answer (for moving rows from active->deleted) but figured I'd keep it simple. @ThiefMaster - writeable CTEs are possible in SQLAlchemy too (with a little effort :) – Dave Jones Mar 22 '15 at 13:06
2

One way to think of this would be that you have two classes of user which have slightly different rules: deleted and not deleted. Deleted users e-mails may clash, not deleted users e-mails must be unique.

Because these two classes have different rules (i.e. constraints), instead of using a flag to indicate whether or not a user is deleted, I would duplicate the tables: one set for deleted users, one for not deleted users. Then I would simply create the unique constraint on the table for not-deleted user's e-mails.

Advantages:

  • Under the assumption that most operations would be against "not deleted" users, this model offers better performance. The "not deleted" users tables only ever represent the current set of active users, so they grow and shrink accordingly. The deleted users tables only ever grow (unless you permit a user to be undeleted).
  • Querying all users is still relatively trivial and if you want to make it as trivial as before you can simply make a view which unions the two sets (and which generates an is_deleted flag depending on the source of the row).
  • This doesn't rely on CREATE INDEX...WHERE. The reason I mention this is that partial indexes aren't universally supported (e.g. MS-SQL and DB2). Obviously you've got no problems with PostgreSQL, but it never hurts to avoid potential future migration hurdles.

Disadvantages:

  • More complicated to deleted (or undelete) users. Instead of flipping a simple flag field you've now got to move rows. However, you've mentioned you're using SQLAlchemy (good choice!). If I recall correctly, this has no trouble constructing statements like INSERT..SELECT FROM which should make moving entries from not deleted to deleted (or vice versa) simple.
  • Twice as many tables for users (+ optional views for unions). This won't affect performance, but it might complicate any schema diagrams!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.