Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: Constraints

  1. #1
    New Coder
    Join Date
    Nov 2008
    Location
    Melbourne, Australia
    Posts
    69
    Thanks
    8
    Thanked 1 Time in 1 Post

    Constraints

    hey all,
    I was wondering if there is such a bit of code that can check the date of birth against the current date and if there is 18+ years stores the data but if its under 18 theres an error?

    at the moment this is my code. it creates the table and inserts both entries.
    Code:
    create table Cust
    DateOfBirth DATETIME NULL CHECK (DateOfBirth < DATEADD(yy, -18, GETDATE()));
    
    
    
    INSERT INTO Cust (DateOfBirth) VALUES (26-04-1988);
    INSERT INTO Cust (DateOfBirth) VALUES (26-04-1998);
    any help would be greatly appreciated

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by kryssy23 View Post
    I was wondering if there is such a bit of code that can check the date of birth against the current date and if there is 18+ years stores the data but if its under 18 theres an error?
    nope. should be done in your server-side scripting language.

    at the moment this is my code.
    Code:
    create table Cust
    DateOfBirth DATETIME NULL CHECK (DateOfBirth < DATEADD(yy, -18, GETDATE()));
    
    INSERT INTO Cust (DateOfBirth) VALUES (26-04-1988);
    INSERT INTO Cust (DateOfBirth) VALUES (26-04-1998);
    Try this
    Code:
    create table cust
    ( date_of_birth DATE not null) engine.....
    
    insert into cust
    ( date_of_birth)
    values ( '1998-04-26')
    I think there's little point in making it a DATE column and then not storing in date format.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • Users who have thanked bazz for this post:

    kryssy23 (05-26-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,202
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    You *could* make such a check if you used a Stored Procedure to do the INSERT.

    The SP would return a status of some kind indicating if the INSERT was done or not.

    Many DB purists would argue that you should *ALWAYS* use Stored Procs when adding/updating your database, just so that you can build in data integrity checks. Granted, that's usually done when the DB administrator and the application programmer are different people or even different teams--not when all the work is done by one person--but even for a one person shop it's not a bad idea, to protect against your own future mistakes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    To be clear MySQL does not support CHECK CONSTRAINTs at all.


  •  

    Posting Permissions

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