Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MySQL

Options
  • 29-11-2010 12:32am
    #1
    Cork24
    Closed Accounts Posts: 2,663 ✭✭✭


    Quick Question
    i have a Table called Workson and went to allow users to enter in their hours worked between 0 to 40

    so far i have

    CREATE TABLE WorksOn

    (empNo VARCHAR(4) NOT NULL,
    projNo VARCHAR(10),
    dateWorked datetime,
    hoursWorked VARCHAR(2)
    CHECK(hoursWorked IN('0<=40')),
    PRIMARY KEY (empNo, projNo, dateWorked));

    i know
    CHECK(hoursWorked IN('0<=40')),
    is totally wrong but searching Google like a mad thing. My Guess Google's shares are after Sky Rocketing after me over the past weekend.


Comments

  • Options
    #2
    castie
    Hosted Moderators Posts: 3,807 ✭✭✭castie


    Have you tried the below?

    CHECK(hoursWorked >0 AND hoursWorked <40)),

    If this doesn't work you could also define a function that takes a value and returns a 0 or 1 if its in the range or not and call this function in your check.

    Sorry but Im not near anything that I can test these out on.


  • Options
    #3
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    Cork24 wrote: »
    CHECK(hoursWorked IN('0<=40')),

    Could it be CHECK (hoursWorked<=40)?

    (haven't worked on MySQL for a long time)


  • Options
    #4
    ron_darrell
    Users Awaiting Email Confirmation Posts: 351 ✭✭ron_darrell


    Would a simpler fix not be to make hoursWorked of type int instead of type varchar?


  • Options
    #5
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    ron_darrell wrote: »
    Would a simpler fix not be to make hoursWorked of type int instead of type varchar?
    Indeed, you could make it an UNSIGNED TINYINT and then they wouldn't be able to input values less than zero.

    Oddly, I've never used the CHECK() statement because I prefer to do my checking in-code (bad practice :D), but it seems to me that the below should work for you, especially if hoursWorked is an integer field and not a character one:

    CHECK(hoursWorked BETWEEN 0 and 40)


  • Options
    #6
    castie
    Hosted Moderators Posts: 3,807 ✭✭✭castie


    castie wrote: »
    Have you tried the below?

    CHECK(hoursWorked >0 AND hoursWorked <40)),

    If this doesn't work you could also define a function that takes a value and returns a 0 or 1 if its in the range or not and call this function in your check.

    Sorry but Im not near anything that I can test these out on.

    Oops didnt spot it was a varchar!


  • Advertisement
  • Options
    #7
    azzeretti
    Registered Users Posts: 1,477 ✭✭✭azzeretti


    I am surely reading OP's question wrong. But why would you do this at the db level and not in the code. It would be far easier to check your input from the app to see if it is within range? Surely?


  • Options
    #8
    Creamy Goodness
    Registered Users Posts: 26,579 ✭✭✭✭Creamy Goodness


    it's an extra fail safe.

    imagine you have a database in which 50 developers are working on, having the schema check the value is better, as they'll soon learn the constraints instead of you having to tell them all individually that that value needs to be contrained.

    although that being said i'm not suggesting for one minute that you don't also santitise and check any values that come even remotely close to touching a db.


Advertisement