MySQL: check constraint using date

Question

I am using MySQL and here is a simple set of queries:

 create table dcheck ( fdate date, sdate date, check (fdate <= sdate) ); insert into dcheck values ('2006-12-12','2003-12-12'); insert into dcheck values ('2003-12-12', '2006-12-12'); 

Here I expect the first insert statement to fail. But it is surprising that both queries pass and two tables are in the table.

Can someone explain why?

thanks

Answer 1

MySQL does not use CHECK restrictions. From the last (5.6) excellent guide :

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

So, the syntax is parsed for compatibility with other other SQLs, but the validation fails.

You could fake a CHECK constraint using the FOREX INSERT and BEFORE UPDATE triggers, which threw an exception if the desired condition was not met.

Answer 2

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

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Answer 3

CHECK restrictions are now supported since MySQL 8.0.16