Re: Check Constraints

From: Ken Denny <ken_at_kendenny.com>
Date: Sun, 17 Mar 2002 14:03:24 GMT
Message-ID: <Xns91D45D2F8BFC7kendenny@65.82.44.7>

dkniveton71_at_msn.com (casper44) wrote in news:be3be537.0203170526.a793d85_at_posting.google.com:

> How do I check to make sure a attribute of type VARCHAR2 is greater
> than 2 characters? For example if I create a table and the attribute
> is:
>
> organization VARCHAR2(50) NOT NULL,
>
> and I also want it to always be greater than 2 characters.
>
> Thanks.

The only way I know to do that is to use a trigger:

CREATE OR REPLACE TRIGGER check_org_2

   BEFORE INSERT OR UPDATE OF my_table
   FOR EACH ROW
BEGIN
   IF INSERTING OR UPDATING(organization)    THEN

      IF LENGTH(:new.organization) <= 2
      THEN
         RAISE_APPLICATION_ERROR(-20000,
            'Organization must be > 2 characters');
      END IF;

   END IF;
END; I haven't worked with 9i. It's possible it has a capability to do this without using a trigger.

-- 
Ken Denny
http://www.kendenny.com/

As you make your way through this hectic world of ours, set aside a few 
minutes each day.  At the end of the year, you'll have a couple of days 
saved up.

Received on Sun Mar 17 2002 - 08:03:24 CST