CODEWITHSUNDEEP
mysqlsqldate
bdhar
bdhar

Reputation: 22983

MySQL: Check constraint with Date

I am using MySQL and here is a simple query set:

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 surprisingly, both the queries passes and two rows are there in the table.

Can anyone please explain why?

Thanks

Upvotes: 4

Views: 7067

Answers (3)

lefred
lefred

Reputation: 29

CHECK constraints are now supported since MySQL 8.0.16

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65284

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

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

Upvotes: 2

mu is too short
mu is too short

Reputation: 434685

MySQL doesn't implement CHECK constraints. From the latest (5.6) fine manual:

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

So the syntax is parsed for compatibility other other SQLs but the checking is not implemented.

You could fake your CHECK constraint with BEFORE INSERT and BEFORE UPDATE triggers that threw an exception if the desired condition was not met.

Upvotes: 12

Related Questions