Is it possible in ASA 7 and beyond to have a column check constraint
that uses another column in the same table in the criteria?

That is something like (constraint on column col1):

CHECK (CASE col1
WHEN 'Y' and (col2 IS NULL or col3 IS NULL) THEN FALSE
ELSE TRUE
)

The reason I'd like to do this is to help debug a problem in our code
which the developers have not been able to pin point, that is, a
condition that they are not able to find on their own, I'd like the
database to catch it and not allow a certain record format.

I tried the above logic in a alter table check statement, but the
database doesn't like the condition, and I haven't been able to find a
related example in the ASA manual.

Thanks.

Phil

Try a *table* check constraint.

If you still have trouble with the ALTER TABLE post the exact code and
the exact error message.

Breck

On 5 Dec 2003 06:18:18 -0800, Phil Kaufman
<philk (AT) dbcsmartsoftware (DOT) com> wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

You need to specify your restriction as a table constraint and not a column
constraint. Something like the following as a table constraint should do
what you want:

CHECK ( (col1 <> 'Y') or ( col2 is not null and col3 is not null ))

-anil

"Phil Kaufman" <philk (AT) dbcsmartsoftware (DOT) com> wrote

anil goel (ASA Development) wrote:

Thanks guys, that is what I was looking for.

Phil