I have a table with partition and sub partition, I want to apply a check constraint on the sub partition column, i am not sure why my case statement is failing with "[Error] Execution (20: 1): ORA-00920: invalid relational operator"
CREATE TABLE test2
(
my_date date,
mypos varchar2(100),
chk_flag varchar2(1)
) TABLESPACE system
PARTITION BY RANGE (my_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (chk_flag)
SUBPARTITION TEMPLATE(
SUBPARTITION P_ISO VALUES ('Y') TABLESPACE system,
SUBPARTITION P_NON VALUES ('N') TABLESPACE system
)
(PARTITION my_example VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')));
alter table test2 add constraint chk_fl
CHECK ( CASE WHEN mypos = 'MANAGER' THEN 'Y' ELSE 'N' END
)
Also, my understanding is, going for a constraint is better than a trigger since trigger slows down the inserts and selects, am I correct ?