0

I'm trying to add a check constraint on MySQL to only allow valid email addresses and from what I have already syntax appears to be: Alter Table book Add Constraint CheckEmail Check (email Like '%@%._%')

My table is book and it has a column called email that has the email addresses.

Other link seems to confirm this should be correct: TSQL Email Validation (without regex)

Does this look ok?

This executed correctly but after this I can add an entry 'h'. Any ideas what's wrong here?

I'm on mySQL 5.1.

Community
  • 1
  • 1
daithi_dearg
  • 47
  • 1
  • 2
  • 9
  • 1
    The best idea (I personally believe) would be to check your email input with whatever language you are using to make your queries, like php. You can even check against the DNS to make sure the hosting is real. – Ryoku Mar 21 '13 at 23:27
  • I've read elsewhere that the best idea is to limit this on the database itself but I've written this in PHP so if other people don't come back I might try looking at that. I don't think I'd go as far as checking the DNS but appreciate your ideas. – daithi_dearg Mar 21 '13 at 23:33
  • In this article you can find the logic behind a full function to trully validate an email address with php and the function itself. I hope it helps if you do go with this solution. http://www.linuxjournal.com/article/9585?page=0,3 – Ryoku Mar 21 '13 at 23:45

2 Answers2

2

Yes, you can since MySQL 8 using a check constraint:

ALTER TABLE `user` 
ADD CONSTRAINT `user.email_validation` 
    CHECK (`email` REGEXP "^[a-zA-Z0-9][a-zA-Z0-9.!#$%&'*+-/=?^_`{|}~]*?[a-zA-Z0-9._-]?@[a-zA-Z0-9][a-zA-Z0-9._-]*?[a-zA-Z0-9]?\\.[a-zA-Z]{2,63}$");
Jacob Thomason
  • 3,062
  • 2
  • 17
  • 21
0

MySQL does not actually enforce check constraints. It won't barf if you include them, but it will not enforce their checking, either. This was answered with a bit more detail elsewhere on StackOverflow [1] and many times in the MySQL forums including both discussion [3] and bugs [2]. Note that MySQL does support enforcement of foreign key constraints and UNIQUE. Use InnoDB for this.

Community
  • 1
  • 1
Megan Squire
  • 1,001
  • 7
  • 18