SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    )

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm surprised MySQL doesn't have a more direct way of doing this.

    Interesting info. Thank you both.


Bookmarks

Posting Permissions

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