Need help...constraints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Need help...constraints

  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Lightbulb Need help...constraints

    Hello, first let me thank you in advance for looking/helping me out.
    For Oracle 8i.

    I need to do 3 seperate constraints all on the table level.

    1. Not allow a CHAR field to contain "-"
    2. Linking field "A" from table TABLEA to "B" from TABLEB
    3. Restricting a closing day to be between 1-28 for the month.

    I need all the help I can get, as big or as little.

    Thank you very much,
    Erik

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Check example on Forein Key/Check constraints in oracle documentation link after the syntax part which gives an idea how to enforce your requirements.

    http://download-west.oracle.com/docs...4a.htm#2061612
    Reddy,Sam

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Thanks. I got the last 2...the first one is still a problem.

    I tried several things, can't get it to check.

    Assuming it's...

    CREATE TABLE SS
    (SS_NUM CHAR(9));

    Now, I need it to fail on "-".
    I tried:

    CONSTRAINT SS_NUM_FAIL CHECK (SS_NUM BETWEEN 0 - 999999999)

    I also tried the one with (SS_NUM [0-9][0-9]....) {Fogot the Syntax}

    Neither worked...any ideas? I need it to be a 9 digit number that doesn't allow dashes {"-"}

    Thanks a million.

    Erik

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Nine-digit number check would be ...
    Code:
    check (translate(my_col,'123456789','000000000') = '000000000')
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    This is what I ended up using, thanks for the help everyone...

    SS_NUM CHAR(9) CONSTRAINT SS_NUM_NOT_VALID check (NOT SS_NUM LIKE ('%-%'))

    NOT NULL,

    Thanks again,
    Erik

Posting Permissions

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


Click Here to Expand Forum to Full Width