Search code examples
mariadbcheck-constraint

Check constraint not working in mariaDB

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?

Solution

  • 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.