how to create check constraint in maria DB for checking char type multiple values?

Question

i have working for my college project of QuestionAnswer site like StackOverflow but i have face one problem in creating tables in Maria DB

i use latest version of XAMPP which by default use maria DB instead of MYSQL

so i want to create post table which contain post type (p_type) like (question, answer, comment),

i use following code

create table post
(
 p_type char(1) check(p_type = 'q' OR p_type = 'a' OR p_type = 'c')
);

and i use InnoDB storage engine and Maria DB version is 10.1.30
but when i insert other character like (s,z,x) is store in database which means Check constraint is not applied,

i also visited the Maria DB manual for Check Constraint but there is no any example related to Char type.

so any answer would be appreciated thanks in advance

Answer 1

Your syntax is fine, the version is wrong. MariaDB versions before 10.2 (and all available versions of MySQL) parse the CHECK clause, but ignore it completely. MariaDB 10.2 performs the actual check:

MariaDB [test]> create table post ( p_type char(1) check(p_type = 'q' OR p_type = 'a' OR p_type = 'c') );
Query OK, 0 rows affected (0.18 sec)

MariaDB [test]> insert into post values  ('s');
ERROR 4025 (23000): CONSTRAINT `p_type` failed for `test`.`post`

MariaDB [test]> insert into post values  ('q');
Query OK, 1 row affected (0.04 sec)