I have a simple insert statement like below
x
Insert into table_X (id, validFrom, validTo, someValue) VALUES(?,?,?,?);
where validFrom and validTo have a DATE type.
I want to insert records to table only if validFrom <= validTo
. How can I add some condition checking to the SQL statement to do so? For now I’m doing the cheking with java and I want to do it in the sql query if possible.
EDIT
CREATE TABLE XYZ.TABLE_X
(
ID VARCHAR2(20 BYTE) NOT NULL ,
VALIDFROM DATE NOT NULL ,
VALIDTO DATE NOT NULL ,
SOMEVALUE NUMBER(18, 0) NOT NULL ,
CONSTRAINT TABLE_X_PK_N2 PRIMARY KEY
(
ID ,
VALIDFROM ,
VALIDTO
)
ENABLE
CONSTRAINT chk_table_x_valids
(
check(VALIDFROM <= VALIDTO )
)
ENABLE
)
ORGANIZATION INDEX
LOGGING
TABLESPACE XYZ
Advertisement
Answer
You can add a check
constraint:
alter table table_x add constraint chk_table_x_valids
check (validFrom <= validTo);