ORACLE Check condition between 2 columns on a table.

[Manuel G]

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.

[Jean-Luc Oudart]

Hi,

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


Jean-Luc

[Christian Gebhardt]

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]

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]

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.

[Barbara Spadaro_1]

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

[Graham Cameron_1 · Accepted solution]

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)))
)))
/