1

I have a table named problem. It has a column name type. I want to limit the user by allowing him to enter only six values. When I write my code, it says that my query has been successfully added by it still isn't working. I can still add values outside my constraints.

My code has been given below.

alter table problem
add constraint chk_type check (type in (
    'adhoc', 'mathematics', 'graph_theory', 'data_structure',
    'dynamic_programming', 'computational_geometry'
));
halfer
  • 19,824
  • 17
  • 99
  • 186
odbhut.shei.chhele
  • 5,834
  • 16
  • 69
  • 109
  • 2
    Well, you can have them there, but they won't work by design, as MySQL ignores check constraints. I'd recommend creating a different table (eg: `types`) and enforce a foreign key constraint. You could also use triggers, but I'd rather not go there. – NullUserException Nov 14 '12 at 19:49
  • 1
    @NullUserException: Your comment is a fine answer IMHO. – juergen d Nov 14 '12 at 20:00
  • -1 : It's all over the manual, it's all over the web, and it's even in SO. Check constraints are parsed, but ignored by all engines in MySQL. This really didn't need to be asked here, google would have answered it for you. – MatBailie Nov 14 '12 at 20:16
  • @Dems I disagree. The functionality is there, and you can add it with no problems. Someone who's not familiar with this bizarre design choice and adds a check constraint only to find that it doesn't work wouldn't know immediately where to look. I'd probably think there's a problem with my syntax or something. +1 to counteract -1 – NullUserException Nov 15 '12 at 00:10
  • @NullUserException - `wouldn't know immediately where to look`??? The Manual? Google? Search SO??? – MatBailie Nov 15 '12 at 08:15
  • 1
    Related SO question - http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working – Joseph B Jun 26 '14 at 01:40
  • MySql does not enforce check constraints. they are simply ignored. terrible behavior to enable you to write them and not even hint that they are meaningless. – Zohar Peled May 08 '16 at 07:03

1 Answers1

1

Try this one : 

ALTER TABLE passager ADD CONSTRAINT statut_chk CHECK statut in ('Frequent Flyer','Occasionnel','Junior');
il_raffa
  • 5,090
  • 129
  • 31
  • 36
foreal
  • 26
  • 1