Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    18

    Unanswered: Help with Check Constraint syntax please :)

    Hia,

    I want to add a check constraint to prevent inputs of just a space or one character ...

    ALTER TABLE BOOK
    ADD CONSTRAINT BOOK_CK
    CHECK(AUTHOR ......

    ....Denotes where I need help with what syntax to use

    Thanks in advance !

    Greg

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This will prevent entering a value for the "author" column which is less than 2 characters in length:

    ALTER TABLE book ADD CONSTRAINT book_author_ck
    CHECK (LENGTH(author) > 1);

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    To catch spaces also:

    ALTER TABLE book ADD CONSTRAINT book_author_ck
    CHECK (NVL(LENGTH(TRIM(author)),0) > 1);

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Isn't space a character too? So, its length is 1 (one), right?

    I tried both solutions - yours and mine - and the difference is that your constraint will not allow insert of a NULL character (''); both constraints prevent insert of a space (' ').

    Although your constraint does more things at once, I think that preventing NULL inserts should rather be done using the NOT NULL constraint.

    Does this make any sense? What do you think, Tony?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    True. I was preventing input of 2 or more spaces also, or a single non-blank plus space entry like 'x ' - which wasn't explicitly asked for! Yours does what was asked.

    I presume the idea is to force users to type something meaningful. In the end, all such attempts are futile because they will just learn to type '..' or whatever. Even this forum tries to do that, forcing posts to contain at least 10 characters. Sometimes I try to answer a question with a pithy response like "Why?", and it gets rejected. So I might change it to something like "Why?????????"

Posting Permissions

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