Re: Help needed enforcing constraints..

Dan Roberts wrote:
>
> Hi Netters
>
> .. I am trying to enforce a constraint on making sure that an order number
> is entered in correctly.. IE.. It must have format values
> [A-Z][A-Z][A-Z][0-9][0-9] in order to be a valid number. so if someone
> were to enter in an order number as lets say bmss3
> or b3m91 they would get an error msg..becuase the correct format would
> have to be bms99 in this case. Now I have figured
> out how to do this in a model example below..but it is very poor since I
> had to make each character a different attribute and
> enforce the constraints one at a time..and in the long run I would end up
> with a huge concatenated primary key...
> Is there a way to enforce a constraint such as the value entered must
> have the format of [a-z][a-z][a-z][0-9][0-9]
> when it is entered in by the user? Can I do this at the create table
> level command?? thanks for the help!!..Dan
>
> create table customer2
> (first_pos char(1) not null constraint first_pos check
> (first_pos between ('A') and ('Z')),
> second_pos char(1) not null constraint second_pos check
> (second_pos between ('A') and ('Z')),
> first_num number(1) not null constraint first_num check
> (first_num between (0) and (9)))
>
> --
> Dan Roberts
> BRISTOL-MYERS SQUIBB PHARM. RES. CENTER "Nature is last at Bats"
> PRINCETON, NEW JERSEY U.S.A. PLANET EARTH, MILKY-WAY GALAXY
> <<<<INTERNET ADDRESS>>>>Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM

Try

create table customer2 (key varchar2(5)

   constraint check_key check

        (translate (a, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
                       'AAAAAAAAAAAAAAAAAAAAAAAAAA0000000000') =

Carlo Bies Received on Tue Aug 27 1996 - 00:00:00 CEST