5

I have an email field that's optional. The regex works fine accepting the empty string (^$) but now the issue is that the empty string is considered a unique entry. It will only allow one user to register without entering an email address. I know I have to set it to null, but not sure how.

Something like this:

Duplicate entry '' for key 'users_email_unique' empty field

Error: Duplicate entry '' for key 'email'

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Yuck Fou
  • 61
  • 2
  • 11

2 Answers2

9

You could use partial index:

CREATE UNIQUE INDEX idx_unq_tab_email ON tab(email) WHERE TRIM(email) <> '';

DBFiddle Demo

That way you still have UNIQUE constraint plus original value.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • The downside of this is that all `email is null` queries would have to become `email is null or trim(email) = ''`. I think you'd be better off in the long run if you cleaned up the data before putting it in the table. – mu is too short Mar 03 '18 at 19:38
2

Create a trigger that converts blanks to nulls on insert or update:

create trigger null_email
before insert or update on users
for each row
new.email = nullif(old.email, '')

Or convert blanks to nulls on insert:

insert into users (..., email, ...)
values (..., nullif(?, ''), ...)

The trigger is the better way, because it handles data from any source, whereas method 2 would require the insert/update SQL of every application to conform to the "no blanks" rule.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • I'd agree with you on this, papering over the problem with a partial index seems like a gift that keeps on giving (grief and confusion). – mu is too short Mar 03 '18 at 19:41