SQL, constraint CHECK

Question

I'm creating my first mysql script and want to create a table with column 'type'. it should has only two possible values: 'XYZ' and 'ABC', so I made:

CREATE TABLE table(
type varchar(12) NOT NULL,
CHECK (type = 'XYZ' or type = 'ABC');

But when i'm testing and inserting values like 234 or 'John' it's working and doesn't return any errors. I think it shouldn't insert value 'John', is it?

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

MySQL does not support check constraints:

The CHECK clause is parsed but ignored by all storage engines.

So while the check statement does not give an error, it is silently ignored.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 24 '12 at 16:55

Andomar

232,371
49
380
404

Hah. There seems to be a lot of things like that in MySQL. Syntax it lets you write that doesn't actually do anything. I'd prefer it to be noisy by default and say when things aren't going to do anything. – theon Nov 24 '12 at 17:07
@theon: you can always upgrade to PostgreSQL ;) – Nov 24 '12 at 17:08
@a_horse_with_no_name :D We use both where I work, so I get to complain about them both equally. – theon Nov 24 '12 at 17:12

score 1 · Answer 2 · answered Nov 24 '12 at 16:56

1

Use an enum instead:

CREATE TABLE test( 
  type ENUM('XYZ', 'ABC')
);

See this SQL fiddle. Try changing the insert to 'John' and you will get an error:

answered Nov 24 '12 at 16:56

theon

14,170
5
51
74

+1 Nice, a bit less flexible than check constraints but fits the OP's problem – Andomar Nov 24 '12 at 16:58
I used ENUM earlier, but I was just interested in whether I can use also CHECK and how i can do that. But also thanks – John Kowalski Nov 25 '12 at 10:29