Re: How do I enforce this business rule?

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 18 Jan 1999 21:58:56 GMT
Message-ID: <36a3ae8e.1331814@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 Received on Mon Jan 18 1999 - 15:58:56 CST