Home » SQL & PL/SQL » SQL & PL/SQL » complex constraint
( ) 1 Vote
| complex constraint [message #596917] |
Fri, 27 September 2013 08:24  |
 |
raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA
|
Member |
|
|
I have folliwng table
CREATE TABLE abcd
(
trans_type VARCHAR2(10) nn,
original_tran_no NUMBER,
original_line_item NUMBER(20),
original_trans_dt DATE);
I have to create a constraint
when trans_type = 'R'
then (original_tran_no and original_trans_dt ) should be not null
else can be null able;
is the follwing stmt right?
alter table abcd add constraint t1_chk1
check (
( case when trans_type = 'R' then (original_tran_no is not null
and original_trans_dt is not null))
);
Thanks in advance
|
|
|
|
| Re: complex constraint [message #596919 is a reply to message #596917] |
Fri, 27 September 2013 08:46  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
raopatwariyahoocom wrote on Fri, 27 September 2013 15:24I have to create a constraint
when trans_type = 'R'
then (original_tran_no and original_trans_dt ) should be not null
else can be null able;
is the follwing stmt right?
alter table abcd add constraint t1_chk1
check (
( case when trans_type = 'R' then (original_tran_no is not null
and original_trans_dt is not null))
);
No, as you already figured out when trying to run it (otherwise you would not be asking this question).
CASE expression returns expression, so THEN clause cannot contain condition. As BOOLEAN data type is not persent in SQL, Oracle distinguishes between these two language elements.
However, what using a simple logic and make the compound condition based on it:
(trans_type != 'R') or (original_tran_no is not null and original_trans_dt is not null)
?
|
|
|
|
|
|
|
|
| Re: complex constraint [message #596951 is a reply to message #596922] |
Fri, 27 September 2013 14:41 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 27 September 2013 10:04
Generally speaking, an expression "if A then B" is equivalent to "(not A) or B".
In you case B is in the form "C and D" but this does change anything.
???
SY.
|
|
|
|
| Re: complex constraint [message #596954 is a reply to message #596928] |
Fri, 27 September 2013 15:02 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
raopatwariyahoocom wrote on Fri, 27 September 2013 10:48I ahve added the follwing check constraint. So for its working Thanks
alter table a add constraint t1_chk1
check (
( (trans_type != 'R') or
(original_tran_no is not null and original_trans_dt is not null)
) );
The above allows for trans_type = 'R' and any/both original_tran_no and original_trans_dt being null, which violates when trans_type = 'R' then (original_tran_no and original_trans_dt ) should be not null
You need something:
SCOTT@orcl > create table abcd(
2 trans_type VARCHAR2(10),
3 original_tran_no NUMBER,
4 original_trans_dt DATE
5 )
6 /
Table created.
SCOTT@orcl > alter table abcd
2 add constraint t1_chk1
3 check(
4 (
5 trans_type = 'R'
6 and
7 original_tran_no is not null
8 and
9 original_trans_dt is not null
10 )
11 or
12 nvl(trans_type,'x') != 'R'
13 )
14 /
Table altered.
SCOTT@orcl > insert
2 into abcd
3 values('R',null,null)
4 /
insert
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T1_CHK1) violated
SCOTT@orcl > insert
2 into abcd
3 values('R',null,sysdate)
4 /
insert
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T1_CHK1) violated
SCOTT@orcl > insert
2 into abcd
3 values('R',1,null)
4 /
insert
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T1_CHK1) violated
SCOTT@orcl > insert
2 into abcd
3 values('R',1,sysdate)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values('X',null,null)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values('X',null,sysdate)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values('X',1,null)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values('X',1,sysdate)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values(null,null,null)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values(null,null,sysdate)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values(null,1,null)
4 /
1 row created.
SCOTT@orcl > insert
2 into abcd
3 values(null,1,sysdate)
4 /
1 row created.
SCOTT@orcl >
SY.
|
|
|
|
|
|
 Re: complex constraint [message #596961 is a reply to message #596954] |
Fri, 27 September 2013 15:27 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:The above allows for trans_type = 'R' and any/both original_tran_no and original_trans_dt being null,
Not true:
SQL> create table abcd(
2 trans_type VARCHAR2(10) not null,
3 original_tran_no NUMBER,
4 original_trans_dt DATE);
Table created.
SQL> alter table abcd add constraint t1_chk1
2 check (
3 ( (trans_type != 'R') or
4 (original_tran_no is not null and original_trans_dt is not null)
5 ) );
Table altered.
SQL> insert into abcd values('R',null,sysdate);
insert into abcd values('R',null,sysdate)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.T1_CHK1) violated
SQL> insert into abcd values('R',null,null);
insert into abcd values('R',null,null)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.T1_CHK1) violated
SQL> insert into abcd values('R',1,null);
insert into abcd values('R',1,null)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.T1_CHK1) violated
The only point this expression might not take into account is the case "trans_type is null".
The first part of the expression would then be changed to:
( (trans_type is null or trans_type != 'R') or
(original_tran_no is not null and original_trans_dt is not null) )
But in OP's case the column is declared "NN" (Not Null).
[Updated on: Fri, 27 September 2013 15:28] Report message to a moderator |
|
|
|
|
|
| Re: complex constraint [message #596973 is a reply to message #596957] |
Sat, 28 September 2013 06:21 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 27 September 2013 16:12
What don't you understand?
Your statement is true in TRUE/FALSE logic. It is not true in TRUE/FALSE/NULL logic used by oracle. Logic
IF condition1 THEN condition 2
will return NULL if condition1 is NULL regardless of condition2.
NOT condition1 OR condition 2
will return TRUE/FALSE when condition1 is NULL depending of condition2. And that is what I was talking about:
SQL> create table abcd(
2 trans_type VARCHAR2(10) not null,
3 original_tran_no NUMBER,
4 original_trans_dt DATE);
Table created.
SQL> alter table abcd add constraint t1_chk1
2 check (
3 ( (trans_type != 'R') or
4 (original_tran_no is not null and original_trans_dt is not null)
5 ) );
Table altered.
SQL> insert into abcd values(null,null,null);
insert into abcd values(null,null,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."ABCD"."TRANS_TYPE")
SQL> insert into abcd values(null,null,sysdate);
insert into abcd values(null,null,sysdate)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."ABCD"."TRANS_TYPE")
SQL> insert into abcd values(null,1,null);
insert into abcd values(null,1,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."ABCD"."TRANS_TYPE")
SQL>
So you can't say "in general" in Oracle with it TRUE/FALSE/NULL logic. You can only say "if condition1 can never be null". And that's what my ? was all about.
SY.
[Updated on: Sat, 28 September 2013 06:39] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:25:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
