0

I have a simple insert statement like below

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 
...
nopens
  • 721
  • 1
  • 4
  • 20
  • https://stackoverflow.com/questions/39970512/constrains-using-date – upog Apr 16 '20 at 11:35
  • 1
    Where do you get your data from? How do you fill your `VALUES(?,?,?,?)`? Is it possible to filter the input data? Something like `insert into table MYTAB values (1,2,3) if 2 > 3` does not exist in oracle. The only way to reject these inserts is by using check-constraints as you already mentioned (-> causes exception). – Chrᴉz remembers Monica Apr 16 '20 at 11:55

1 Answers1

1

You can add a check constraint:

alter table table_x add constraint chk_table_x_valids
    check (validFrom <= validTo);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your quick response. I have checked how the table was created and found the above script. Can I add your approach after the constraint that is already there? Sorry if it is dumb question. I am unexpierienced. – nopens Apr 16 '20 at 11:42
  • @nopens . . . You can add this constraint to an existing *table*. But if the constraint is already there, there is no need to add it again. – Gordon Linoff Apr 16 '20 at 12:09
  • There is only the first constraint (TABLE_X_PK_N2 PRIMARY KEY ). I have just added the second following your recommendation. I just wanted to make sure that the syntax is correct how I add it. – nopens Apr 16 '20 at 12:15
  • 2
    " I just wanted to make sure that the syntax is correct" Oracle will let you know if syntax is not correct. Don't be afraid to try it for yourself. That's how you move from "I am unexpierienced. " to being a guru. – EdStevens Apr 16 '20 at 12:45