1

I've got a database table which contains an Email field. Somehow an invalid email address has got through application validation, and got into the database field.

How can I add a constraint to this field so that its not possible for an invalid email address to be stored?

Curtis
  • 101,612
  • 66
  • 270
  • 352

2 Answers2

1

It depends a little on what valid means to you. To me, an email address might be valid (that is, it's formed correctly according to RFC 5322), but I might not be able to send to it. Whether you need to test a) the format of the address or b) your ability to send email to it is application-dependent.

If you just want to make sure the user's email address is in the correct format, you're stuck with validation by regular expression. Good luck with that. See RFC 3696, "3. Restrictions on email addresses" for the depressing details.

Off the top of my head, here are some guiding points that I've used in the past to help my clients make decisions about email validation.

  • The user wants to get email from us; assume the user is motivated to correct mistakes. (Accept anything with an "@" symbol and a "." in roughly the right place.)
  • There are privacy issues; send email to the user's address, and require the user to click a link to confirm. (You can still accept anything with an "@" symbol and a "." in roughly the right place. Requires cooperation between the database and web app development.)
  • Local policies are involved. Restrict email addresses to this arbitrary subset of truly valid email addresses. (I've seen this when one company buys out another company, and requires everybody to be assigned a new email address before those email accounts are actually created.)

Depending on which way you go, you'll need a looser or tighter regular expression. In my experience, most of the time most people go with looser validation.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
0

According to this article, SQL Server supports Regular Expressions in CHECK constraints. Couple this with a solid email validation regular expression and you should be good to go.

You can also use LIKE in the CHECK constraint.

Also you can enable CLR Integration in SQL Server and create a CLR function if you are using SQL Server 2005 or higher and call it in the CHECK constraint. The CLR function would handle the regular expression validation.

Ranhiru Jude Cooray
  • 19,542
  • 20
  • 83
  • 128