Hello, I am having an issue with a certain table called 'parts'. Each entry in the parts table has a serial #, a part #, and two dates. The first date is when the particular part was installed on the specified engine. The second (which is not required) is when it was removed. The constraint that has to be on the database is that for every entry that has serial X and part Y, their install and remove dates cannot overlap. For example, the table is allowed to have two entries like these:

Serial# Part# Install Date Removal Date
XXXXX YYYYY 12/10/10 12/15/10
XXXXX YYYYY 12/17/10

But NOT allowed to have two entries like the following, since the second entry's install - remove date range overlaps the firsts':

Serial# Part# Engine# Install Date Removal Date
XXXXX YYYYY ZZZZZZ 12/10/10 12/15/10
XXXXX YYYYY ZZZZZZ 12/13/10

I'm not at all sure how to represent this in the database. Ideally, it would be like having 3 primary keys; the combination of the Serial, Part, and period of time between install and removal would be unique. However, representing the Date part as a key seems impossible. The only way I can think to do it is to use PHP to validate the data when I insert it and just plain have no keys at all, but I would prefer a MySQL solution. Any ideas would be greatly appreciated. Thanks.

mysql doesn't support constraints, so do it at the php end.

You should also use DATE field type and use yyyymmdd or similar for your date types, otherwise you'll need to use STR_TO_DATE function to convert your dates as they are each time you need to run a query on them.

mysql does support NULL and PRIMARY KEY constraints just fine, as well as FOREIGN KEY constraints for InnoDB tables

but yeah, it doesn't support CHECK constraints

The check constraints could be performed via triggers, however, inside the trigger it is not possible to raise custom exceptions to the calling process.