Beginner, using IF/THEN/ELSE inside a CHECK clause ?

Hi,

I have a table with two columns :

            agencyCode and agencyName

I have this constraint that must check that the agencyCode be formatted this way : 'XXX_n', where 'XXX' are the first 2 letters of agencyName and the last letter of nameAgency. 'n' is any positive integer. If agencyName has only two letters, I will put the whole name in there ('XX_n').

I am trying to use a CASE or a IF/THEN clause inside my CHECK clause but I always have an error (missing keyword).

Does this constraint absolutely need to be a trigger ? (I have not learn Triggers yet !).

here's what I try to do (but it is not working):

add constraint
check_xxx_n check( if

                     length(agencyName) < 3
                       then


                           (substr(agencyCode,1,2) =


substr(agencyName,1,2)
                       else


                           (substr(agencyCode,1,2) =


substr(agencyName,1,2)


                           (substr(agencyCode,3,1) =


substr(agencyName,length (agencyName))
                       end
                  );
 

*I have not thought about the positive integer yet :(

thank you if you can give me some help ! Received on Mon Nov 04 2002 - 17:50:12 CET