MySQL - Adding CHECK to a Column

Question

Another what I am sure is a very basic MySQL question.

Create Table MyTable 
(
    zip char(5) CHECK (zip = '11111')
);

Insert into MyTable
Select '99999';

SELECT * From MyTable;   --results in '99999'

I restrict 'zip' to only the value '11111', but it lets me put in '99999' no problem. What am I missing?

In MySQL, you need to implement such constraints using triggers or foreign key references. — Gordon Linoff, Sep 26 '14 at 00:58

score 1 · Accepted Answer · answered Sep 26 '14 at 00:47

1

https://dev.mysql.com/doc/refman/5.6/en/create-table.html says:

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

That is, it accepts the syntax without error or warning, but does not store the constraint and does not prevent you from inserting data that does not satisfy the check.

This feature has been requested since at least 2004, but never implemented: http://bugs.mysql.com/bug.php?id=3464

answered Sep 26 '14 at 00:47

Bill Karwin

538,548
86
673
828

Thank you so much. I'm finding MySQL to be... interesting this way. – J Brun Sep 26 '14 at 00:56

MySQL - Adding CHECK to a Column

1 Answers1