Re: Conditional Constraint?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 22 Dec 2002 14:12:16 -0000
Message-ID: <au4h8o$m6h$1$8302bc10@news.demon.co.uk>

Assuming demo_code is declared not null (which seems a possible constraint that you have not mentioned) and points is declared not null (which is less likely), then the following would seem appropriate. For variations allowing nulls, you have to be more precise in your definition of what combinations are legal - e.g. is points allowed to be null when dem_code is not (and vice versa).

alter table demerit
add constraint dem_pts_ck
check ((dem_code > 20 and points in (1,2,3)) or (dem_code <= 20 and points in (1,2,3,4,5,6))

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





wing wrote in message
<873e96d6.0212211735.4fd633d5_at_posting.google.com>...


>Hi,

>

>I am new in Oracle and have a query on how to add conditional

>constraint.

>

>Say, I have a simple table Demerit with three fields.

>

>Demerit(DEM_CODE, DEM_DES, POINTS)

>where

>DEM_CODE (N, 2)

>DEM_DES (C, 30)

>POINTS (N, 1)

>

>How to add the following constraint?

>

>All DEM_CODE should carry an integer POINT between 1 to 6 inclusive,

>and DEM_CODE greater than 20 should carry no more than 3 POINT.

>

>Thanks in advance of any ideas and inputs.

>

>Wing

Received on Sun Dec 22 2002 - 08:12:16 CST