Check constraint not working in mariaDB

Question

I created a table using the following statement.

create table constraint_test(name varchar(20), city varchar(20) not null check (city in ('chennai','vellore')), phone numeric(10));

But when I insert as,

insert into constraint_test values('abcd,'ooty',123456);

it gets stored. How can I restrict it?

Answer 1

How about

city ENUM('chennai', 'vellore')

or maybe

city ENUM('UNKNOWN', 'chennai', 'vellore')

Or you could use a TRIGGER.

Or you could implement the check in your application code. After all, not everything can be done in SQL.