brandonbuster — 2012-07-05T13:32:55-04:00 — #1
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.
id int(11) not null auto_increment primary key,
name varchar(55) not null,
guelphdad — 2012-07-05T14:32:33-04:00 — #2
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.
rcashell — 2012-07-06T04:44:12-04:00 — #3
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.
brandonbuster — 2012-07-06T18:32:57-04:00 — #4
I'm surprised MySQL doesn't have a more direct way of doing this.
Interesting info. Thank you both.