Databases
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.