Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Lightbulb Unanswered: New to Oracle...please help!

    I am trying to write a CHECK CONSTRAINT for an existing database. I need the CONSTRAINT to check that the member_type is either 'member' or 'instructor'. Then I need it to do nothing if member_type is 'member', or if member_type is 'instructor' then CHECK that license_type, date_of_issue and license_num are NOT NULL. I have attempted to write the code below, but I am sure that it is way off!

    ALTER TABLE members
    MODIFY (member_type VARCHAR2(3) CONSTRAINT members_member_type_conform
    CHECK (member_type IN ('member','instructor')),
    WHEN ('member'),
    CHECK (license_type, date_of_issue, license_num)) NULL,
    WHEN ('instructor'),
    CHECK (license_type, date_of_issue, license_num)) NOT NULL));

    I also need to write a CHECK CONSTRAINT to check that the first two letters of a site_code are UPPER case, and the following 6 digits are between 0-9. Any ideas would be much appreciated!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It appears I have not been keeping up with the latest additions to SQL standards.

    Please post URL which documents use of "WHEN" as part of CONSTRAINT clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Need to look at the manual, but upfront it seems that the comma after the WHEN clause would make it separate from the action.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >upfront it seems that the comma after the WHEN clause would make it separate from the action.
    Please post URL which documents use of "WHEN" as part of CONSTRAINT clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Please post URL which documents use of "WHEN" as part of CONSTRAINT clause.
    Sheesh.

    Quote Originally Posted by AnanthaP View Post
    Need to look at the manual, but upfront it seems that the comma after the WHEN clause would make it separate from the action.
    Normally, WHEN is part of a CASE statement, as in CASE WHEN something THEN this WHEN something THEN that ELSE stuff END. I'm

    Code:
    CHECK (member_type IN ('member','instructor')),
    WHEN ('member'),
    CHECK (license_type, date_of_issue, license_num)) NULL,
    WHEN ('instructor'),
    CHECK (license_type, date_of_issue, license_num)) NOT NULL));
    So what you want is to make sure member_type is member or instructor.

    You have to figure out how to break the problem down. Here's how I recommend it:

    You either want it to be a member *or* an instructor. (Start with the "or" alternatives.

    But if it's a member, certain fields may be null.

    But if it's an instructor, certain fields must not be null.

    So, it's valid if-and-only-if (it's a member) OR (it's an instructor AND those fields are not null).

    Code:
    CHECK (
        member_type = 'member' 
    ) OR (
        member_type = 'instructor'
        AND license_type IS NOT NULL
        AND date_of_issue IS NOT NULL
        AND license_num IS NOT NULL
    )
    Generally, if you're doing logical expressions, it's very straightforward to have your OR alternatives at the top and group the ANDs inside. This actually has a name, it's called Sum of Products form, but the point is that it's a natural way to express it.

Posting Permissions

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