Home » SQL & PL/SQL » SQL & PL/SQL » complex constraint  () 1 Vote
complex constraint [message #596917] Fri, 27 September 2013 08:24 Go to next message
raopatwariyahoocom
Messages: 28
Registered: October 2011
Location: GA
Junior 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 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
raopatwariyahoocom wrote on Fri, 27 September 2013 15:24
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))
);

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)

?
icon3.gif  Re: complex constraint [message #596922 is a reply to message #596917] Fri, 27 September 2013 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

[Updated on: Fri, 27 September 2013 09:05]

Report message to a moderator

Re: complex constraint [message #596928 is a reply to message #596922] Fri, 27 September 2013 09:48 Go to previous messageGo to next message
raopatwariyahoocom
Messages: 28
Registered: October 2011
Location: GA
Junior Member
I 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)
) );
Re: complex constraint [message #596951 is a reply to message #596922] Fri, 27 September 2013 14:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2050
Registered: January 2010
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2050
Registered: January 2010
Senior Member
raopatwariyahoocom wrote on Fri, 27 September 2013 10:48
I 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.
icon5.gif  Re: complex constraint [message #596957 is a reply to message #596951] Fri, 27 September 2013 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Solomon Yakobson wrote on Fri, 27 September 2013 21:41
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.


What don't you understand?

icon2.gif  Re: complex constraint [message #596961 is a reply to message #596954] Fri, 27 September 2013 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
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 #596966 is a reply to message #596961] Fri, 27 September 2013 19:31 Go to previous messageGo to next message
raopatwariyahoocom
Messages: 28
Registered: October 2011
Location: GA
Junior Member
Thank you guys, I really really appreciate your help.
Re: complex constraint [message #596973 is a reply to message #596957] Sat, 28 September 2013 06:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2050
Registered: January 2010
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

icon2.gif  Re: complex constraint [message #596975 is a reply to message #596973] Sat, 28 September 2013 07:19 Go to previous message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Your statement is true in TRUE/FALSE logic. It is not true in TRUE/FALSE/NULL logic used by oracle. Logic


Yes, this is what I said in my previous post.


Previous Topic: Returning updated rows with REF CURSOR?
Next Topic: Selecting the correct record from the table
Goto Forum:
  


Current Time: Thu Oct 30 14:26:38 CDT 2014

Total time taken to generate the page: 0.08981 seconds