Adding Constraint to Date Field

I’m wondering how best to add a constraint for a date field that cannot be a future date and cannot be declared earlier than a specified date. Here’s a simplified table structure. I’m using MYSQL.

create table(
id int(11) not null auto_increment primary key,
name varchar(55) not null,
dob timestamp
)

mysql does not support those types of constraints. you would have to check that in your application layer. Also for a DOB field it is more likely you’d use DATE and not TIMESTAMP as a field type.

There are several ways this can be done:

  1. Create a trigger which verifies the dates. However, only from MySQL 5.5 were SIGNAL’s introduces which allows us to raise and exception. Prior to that version the process was to insert into a table that contain a duplicate value causing a duplicate entry exception to be raised.

  2. A stored function which performs the checks and either inserts or not depending on the checks and returns either a 1 or 0 for success or failure.

  3. In your PHP code perform the checks before calling the INSERT statement.

I’m surprised MySQL doesn’t have a more direct way of doing this.

Interesting info. Thank you both.