Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010

    Unanswered: MySQL constraints on DATE

    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

    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.

  2. #2
    Join Date
    Mar 2004
    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.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by guelphdad View Post
    mysql doesn't support constraints
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    San Sebastian, Spain
    The check constraints could be performed via triggers, however, inside the trigger it is not possible to raise custom exceptions to the calling process.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts