mysql: create data constraints

Question

I have a table with two columns:

assign_date | complete_date
----------------------------
2004-04-23  | 2005-05-13
 ...        |   ...

The dates are in the format yyyy-mm-yy.

When I am inserting a new row, I always want the complete_date to be at or after the assign_date. So if a row had an assign_date of 2013-10-10, 2013-10-09 would be invalid. Is there a way to do this in mysql without using triggers?

You need a trigger: While MySQL supports the CHECK clause, currently no storage engine supports it. — Eugen Rieck
– Eugen Rieck, Commented Dec 19, 2013 at 1:02

Bill Karwin · Accepted Answer · 2013-12-19 01:24:18Z

1

No, you must use a trigger to enforce this rule in the database.

Some people validate in their application before inserting the row. But this is error-prone because developers can forget to do the check in every case of inserting or updating data. Or someone can update the data directly without using an application.

answered Dec 19, 2013 at 1:24

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

It isn't clear why the OP has ruled out triggers. Perhaps the question is purely hypothetical, in which case it must be worth at very least noting that one might be able to compile a table of every possibly valid combination and then enforce a composite foreign key constraint. But otherwise I certainly agree that triggers are the correct (and only practical) solution to the described problem in MySQL.

George Newton Over a year ago

Thanks for the answer/comments everyone. Yes, I was engaging in hypotheticals; I wanted to see if there was another practical solution besides triggers.

Collectives™ on Stack Overflow

mysql: create data constraints

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related