Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do I enforce this business rule?

Re: How do I enforce this business rule?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 19 Jan 1999 21:17:31 GMT
Message-ID: <36a6f432.30385932@inet16.us.oracle.com>


On Tue, 19 Jan 1999 20:27:33 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>Would a quicker option be to use translate() twice to translate
>all alpha to 'A' and all numeric to '0' and thn check that the
>result was 'A0A0A0' ?
>
>I can't remember the syntax off-hand, but something like:
>
>check 'A0A0A0' =
>translate(translate (:new.value,'ABC..Z','A'),'0..9',0))
>
>Just an odd thought - doesn't look too convincing though now
>that I've written it out.

That idea works just fine.

eg.

SQL> create table mytable(
  2 id number,
  3 pcode varchar2(6) check ( 'A0A0A0' =

  4          translate( translate( upper(pcode),
  5                     'BCDEFGHIJKLMNOPQRSTUVWXYZ',
  6                     'AAAAAAAAAAAAAAAAAAAAAAAAA' ),
  7                     '123456789',
  8                     '000000000' ) and length(pcode) = 6 ) )
  9 /
Table created.

SQL> insert into mytable values ( 1, 'q1q1a2' ); 1 row created.

SQL> insert into mytable values ( 1, 'q1q1a' ); insert into mytable values ( 1, 'q1q1a' )

                                        *
ERROR at line 1:
ORA-02290: check constraint (CLBECK.SYS_C0034784) violated

SQL> insert into mytable values ( 1, 'q1q1aa' ); insert into mytable values ( 1, 'q1q1aa' )

                                         *
ERROR at line 1:
ORA-02290: check constraint (CLBECK.SYS_C0034784) violated

Hope this helps.

chris.

>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>
>
>Jurij Modic wrote in message <36a3ae8e.1331814_at_news.arnes.si>...
>>On Mon, 18 Jan 1999 17:13:29 GMT, kshave_at_health.gov.mb.ca wrote:
>>
>>>I have a table such as the following ...
>>>
>>>TABLE A
>>>name VARCHAR2(25),
>>>address VARCHAR2(25),
>>>city VARCHAR2(25),
>>>pcode VARCHAR2(6)
>>>
>>>The pcode field is supposed to be a postal code in the format
>>>
>>>A9A9A9 where A is any letter A through Z, and 9 is any digit
>>>0 through 9.
>>>
>>>Without creating INSERT/UPDATE triggers, is there any way to
>>>enforce this business rule? Would a check constraint work?
>>
>>Yes, you can use check constraint to enforce this business rule.
>>
>>ALTER TABLE A ADD CONSTRAINT check_pcode CHECK
>> (SUBSTR(pcode,1,1) BETWEEN 'A' AND 'Z' AND
>> SUBSTR(pcode,2,1) BETWEEN '0' AND '9' AND
>> SUBSTR(pcode,3,1) BETWEEN 'A' AND 'Z' AND
>> SUBSTR(pcode,4,1) BETWEEN '0' AND '9' AND
>> SUBSTR(pcode,5,1) BETWEEN 'A' AND 'Z' AND
>> SUBSTR(pcode,6,1) BETWEEN '0' AND '9')
>>
>>>-Keith
>>
>>HTH,
>>
>>Jurij Modic <jmodic_at_src.si>
>>Certified Oracle7 DBA (OCP)
>>================================================
>>The above opinions are mine and do not represent
>>any official standpoints of my employer
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 19 1999 - 15:17:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US