Re: Integrity Constraint

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Sat, 27 Nov 1999 02:45:43 GMT
Message-ID: <rBH%3.1327$75.77776@news1.rdc2.on.home.com>

Ceri,
You may have two solutions:

1 use a trigger.

    CREATE OR REPLACE TRIGGER TestCol_BEF_INSER     BEFORE INSERT ON TestCol
    BEGIN

        IF Length(:NEW.AAA)<>10 THEN
            RAISE_APPLICATION_ERROR(...);
        END IF;
        -- Other validations here...

    END; 2. Use constraint CHECK

CREATE TABLE TestCol
(

AAA VARCHAR2(10) CHECK (Length(AAA)=10) 
                 CHECK (INSTR('1234567890',Substr(AAA,1,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,2,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,3,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,4,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,5,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,6,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,7,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,8,1))>0)
                 CHECK (INSTR('1234567890',Substr(AAA,9,1))>0)
                 CHECK (INSTR('ABCDEFGHIJKLMNOPRSTUVYZ',Upper(Substr(AAA,10,1)))>0),

BBB VARCHAR2(10)
);

INSERT INTO TestCol (AAA,BBB) VALUES ('123456789A','qwqw'); -- this should work INSERT INTO TestCol (AAA,BBB) VALUES ('12345678AB','qwqw'); -- this should reject.

It is little bit cumbersome but it does the job.

HTH
Thomas Olszewicki
ThomasO_at_cpas.com
www.cpas.com  

Ceri <cerit_at_teleordnospamplease.co.uk> wrote in message news:8E8AA8F3Dcerittownsend_at_172.17.8.4...
> Hi,
>
> I need to check that a valid entry is being made to a field in a table.
> I am running Oracle 8.0.4
>
> The field is a varchar2(11) and the data should be exactly 10 characters
> long, the first 9 being numeric and the last character may be numeric or a
> character.
> Unfortunately, checking this at the client application is not an option.
> The checking needs to be done by the database.
>
> Could anyone give me an idea of how to achieve this please.
>
> TIA
> Ceri
> --
> *- Ceri -*
> ** The views expressed here are entirely my own. **
> To reply b

y e-mail, remove nospamplease from address. Received on Fri Nov 26 1999 - 20:45:43 CST