0

I want to disallow some values to a field of a table. In my example table name is users and field is email. I have added CHECK constraint but I am still able to add these two email addresses to my table. Storage engine - InnoDB. I don't want to change this. I have run the following queries. 

ALTER TABLE users
ADD CHECK (email NOT IN ("admin@gmail.com", "developer@gmail.com"));

or

ALTER TABLE users
ADD CONSTRAINT CHK_UserEmail CHECK (email NOT IN ("admin@gmail.com", "developer@gmail.com"));
user207421
  • 305,947
  • 44
  • 307
  • 483
Sachin Kumar
  • 3,001
  • 1
  • 22
  • 47
  • 2
    Possible duplicate of [CHECK constraint in MySQL is not working](https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) – Paul Spiegel Oct 23 '17 at 21:28
  • But this link says change storage engine or create a trigger. @Paul – Sachin Kumar Oct 23 '17 at 21:36
  • It says that it's not possible yet. But MariaDB 10.2.1 will support it - https://mariadb.com/kb/en/library/constraint/ – Paul Spiegel Oct 23 '17 at 21:41
  • I have no option to select mariaDB under PHPMyAdmin storage engine option box. – Sachin Kumar Oct 23 '17 at 21:43
  • BTW: Where did you read about "change storage engine"? MariaDB is not a storage engine. It's a MySQL compatible DBMS (a fork of MySQL). – Paul Spiegel Oct 23 '17 at 21:44
  • MariaDB 10.2.1 support check constraints. It means I need to change or there is any another option(actually what am I looking for) – Sachin Kumar Oct 23 '17 at 21:47
  • 3
    Well you will need to write INSERT and UPDATE triggers. Or ckeck the emails against a black list on application side. Even if CHECK were supported, I wouldn't use it in your case, because your blacklist may change/grow and you wouldn't want to change your table definitions every time you need to add an email to the list. – Paul Spiegel Oct 23 '17 at 21:56
  • PhpMyADMIN has exactly nothing to do with it. – user207421 Oct 24 '17 at 02:27

0 Answers0