Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

ORACLE Check condition between 2 columns on a table.

SOLVED
Go to solution
Manuel G
Frequent Advisor

ORACLE Check condition between 2 columns on a table.

Hi all:

We want to build a condition on a individual table as follows (values defined are examples):

If (column1=value1) then

column2 can have values (0,1,2,3,4,..,10)

else

column2 can have values (null)

Does anyone knows if Oracle can do this with any check constraint or similar.

Thanks in advance.
6 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: ORACLE Check condition between 2 columns on a table.

Hi,

To me sounds like a trigger :
before insert
before update (unless column1 is your primary key)


Jean-Luc
fiat lux
Christian Gebhardt
Honored Contributor

Re: ORACLE Check condition between 2 columns on a table.

Hi,

From Oracle-Docu

______________________
Restrictions:
The condition of a CHECK constraint can refer to any column in the table, but it cannot refer
to columns of other tables.
CHECK constraint conditions cannot contain the following constructs:
- Queries to refer to values in other rows
- Calls to the functions SYSDATE, UID, USER, or USERENV
- The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
- Date constants that are not fully specified

You have to use triggers.

Christian
Volker Borowski
Honored Contributor

Re: ORACLE Check condition between 2 columns on a table.

Without a testdatabase at home.... :-)

CREATE TABLE table (
....
column1 integer not null,
column2 integer null
CHECK (
(column1=value1)AND column2 in (1,2,3,4)
OR
(column1<>value1) AND column2 is null ),
...
);

or anything near this.

Hope this helps
Volker
Manuel G
Frequent Advisor

Re: ORACLE Check condition between 2 columns on a table.

Hello again:

We want to avoid triggers if we can.

We tried what Volker suggested but it doesnt work very well:

SQL> create table prueba
2 (
3 campo1 integer not null,
4 campo2 integer null
5 CHECK (
6 (campo1=0) and campo2 in (0,1,2,3)
7 or
8 (campo1<>0) and campo2 is null)
9 )
10 ;
)
*
ERROR at line 9:
ORA-02438: Column check constraint cannot reference other columns


SQL> host oerr ora 2438
02438, 00000, "Column check constraint cannot reference other columns"
// *Cause: attempted to define a column check constraint that references
// another column.
// *Action: define it as a table check constriant.

Does anyone know how to define table check constraints as "Action" message says?

Thanks.

Re: ORACLE Check condition between 2 columns on a table.

Hi,

Here's something that might be useful from the Oracle 8i docos:

CREATE TABLE dept
(deptno NUMBER CONSTRAINT check_deptno
CHECK (deptno BETWEEN 10 AND 99)
DISABLE,
dname VARCHAR2(9) CONSTRAINT check_dname
CHECK (dname = UPPER(dname))
DISABLE,
loc VARCHAR2(10) CONSTRAINT check_loc
CHECK (loc IN ('DALLAS','BOSTON',
'NEW YORK','CHICAGO'))
DISABLE);

Note the use of the CONSTRAINT keyword and the fact that each 'check' is named.

cheers
Barbar
If all else fails, read the instructions.
Graham Cameron_1
Honored Contributor
Solution

Re: ORACLE Check condition between 2 columns on a table.

Here's a real world example from one of our 8i tables. (As the DBA I do not advocate such hard-coding of values as best practice....)

CREATE TABLE ABC
(
.
.
.
billing_control_type CHAR(1) NOT NULL,
billing_frequency NUMBER(1),
day_of_period NUMBER(2)
.
.
);

ALTER TABLE ABC
ADD CONSTRAINT abc_dop_chk CHECK (((BILLING_CONTROL_TYPE = 'D' ) AND (
(BILLING_FREQUENCY = 1
AND ((DAY_OF_PERIOD >=1
AND DAY_OF_PERIOD <=28) OR (DAY_OF_PERIOD IS NULL)))
OR
(BILLING_FREQUENCY = 2
AND ((DAY_OF_PERIOD >=1
AND DAY_OF_PERIOD <=91) OR (DAY_OF_PERIOD IS NULL)))
OR
(BILLING_FREQUENCY = 3
AND ((DAY_OF_PERIOD >=1
AND DAY_OF_PERIOD <=182) OR (DAY_OF_PERIOD IS NULL)))
OR
(BILLING_FREQUENCY = 4
AND ((DAY_OF_PERIOD >=1
AND DAY_OF_PERIOD <=91) OR (DAY_OF_PERIOD <= 365)))
)) OR
((BILLING_CONTROL_TYPE = 'K' ) AND (
(BILLING_FREQUENCY = 1
AND ((DAY_OF_PERIOD >=1
AND DAY_OF_PERIOD <=31) OR (DAY_OF_PERIOD IS NULL)))
OR
(BILLING_FREQUENCY = 2
AND ((DAY_OF_PERIOD >=1
AND DAY_OF_PERIOD <=60) OR (DAY_OF_PERIOD IS NULL)))
)))
/
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.