1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Check Constraint for valid Values

Discussion in 'SQL PL/SQL' started by tastybrownies, Nov 4, 2009.

  1. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hey guys, I'm having some trouble with a certain Check constraint I've been trying to write. The user is supposed to be able to enter the days of the week, meaning, either M,T,W,R,F,S, or U. But here's where I'm having trouble, they must also be allowed to enter multiples like MWF.

    Normally I'd just do something like this:
    Code (SQL):
    ALTER TABLE SECTION
    ADD CONSTRAINT SECTION_DAYS_CK
    CHECK (DAYS IN ('M', 'T', 'W', 'R', 'F', 'S', 'U'));
    Is there a way I can do this without having to enter every possible combination of MTW, TWR, and so on?

    Just looking for someone to possibly point me in the right direction.
     
  2. ericb

    ericb Active Member

    Messages:
    28
    Likes Received:
    5
    Trophy Points:
    90
    Location:
    Venlo, The Netherlands
    You can not use user defined functions in a tcheck constraint.

    I would advice you to user a trigger in combination with a function. I will give you example code:

    create or replace function checkdays (p_days varchar2) return boolean
    is
    l_length number(10);
    l_count number(10);
    l_incorrect number(10) default 0;
    begin
    l_length := length(p_days) + 1;
    l_count := 1;
    while l_count < l_length loop
    if substr(p_days,l_count,1) not in ('M', 'T', 'W', 'R', 'F', 'S', 'U')
    then
    l_incorrect := l_incorrect + 1;
    end if;
    l_count := l_count + 1;
    end loop;
    if l_incorrect > 0
    then
    return false;
    else
    return true;
    end if;
    end;
    /

    The trigger then is:
    create or replace trigger CheckInsUpdDays
    before insert or update of days on section
    for each row
    begin
    if not checkdays:)new.days)
    then
    raise_application_error(-20000,'not a valid day or valid days');
    end if;
    end;
    /

    I hope it works for you.

    Kind regards,

    Eric