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:
-
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.
-
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.
-
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.