Hi,
I'm trying to create a check contraint at table level. I tried the following two istructions that in my understanding should have the same effects.
ALTER TABLE table1 ADD CONSTRAINT CHK_1
CHECK ( col1 || col2 <> col3 || col4);
ALTER TABLE table1 ADD CONSTRAINT CHK_2
CHECK ( col1 <> col3 AND col2 <> col4);
The first one works perfectly, running the second one I get
ORA-02290: "check constraint (%s.%s) violated"
Why the second is not accepted?
Many thanks.
Regards,
Antonio
Those aren't even close to being the same!
The second will fail if either condition is false. Add in differences in null handling and concatenation leading to false positives and you've got a whole stack of rows which will accepted one constraint but not the other.
For example, turn them into queries and they give different results:
create table t (
c1 int, c2 int,
c3 int, c4 int
);
insert into t values ( 1, 2, 1, 4 );
insert into t values ( 11, 1, 1, 11 );
select * from t
where c1 || c2 <> c3 || c4;
C1 C2 C3 C4
1 2 1 4
select * from t
where c1 <> c3 and c2 <> c4;
C1 C2 C3 C4
11 1 1 11