Re: Problem with Check Constraint using NVL

Andy Hassall wrote:

> I don't follow the reasoning - could you explain what's wrong with a
> constraint referencing two or more columns in the same row please? Why's a
> trigger preferred? You'd have to cover insert and update of both columns,
> whereas the check constraint is a one-liner enforced at all times by Oracle.

I should have been more clear. There are dangers such as the following which I see you were aware of but that make it very difficult for programs to be adequately tested and maintained.

CREATE TABLE t (
colone VARCHAR2(20),
coltwo VARCHAR2(20));

ALTER TABLE t
ADD CONSTRAINT cc_t
check (colone > coltwo);

INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (NULL, 1);

It is not that you can't do it. Rather that there are some traps and it makes for very difficult to maintain business logic. I should have stated my thoughts as a preference. Sorry.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)