Check the restriction so that the date is not in the future?

Question

I am trying to create a table that records the changes made to the estimated hours of another table. The database as a whole is a project management system for a company that assigns work to its employees and creates invoices for a client.

I currently have:

CREATE TABLE task_history ( task_history_id NUMBER(5), previous_est_hours NUMBER(3,1), change_date DATE, reason_for_change VARCHAR2(50), task_id NUMBER(5), CONSTRAINT TASKHIST_TASKHISTID_PK PRIMARY KEY (task_history_id), CONSTRAINT TASKHIST_TASKID_FK FOREIGN KEY (task_id) REFERENCES task(task_id), CONSTRAINT TASKHIST_TASKID_NN CHECK (task_id IS NOT NULL), CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate) ); 

change_date should not be a future date, it should be either today or in the past. The last check constraint is the problem. As far as I understand, you cannot use sysdate due to a reason that I forgot about, but you cannot. I also tried GETDATE () and every other option I found on the Internet. How can I do this seemingly simple task?

Answer 1

You cannot call a function from a control constraint, so the most natural approach would be to define a trigger in the task_history table, i.e.

 CREATE OR REPLACE TRIGGER task_change_date_in_past BEFORE INSERT OR UPDATE ON task_history FOR EACH ROW BEGIN IF( :new.change_date > sysdate ) THEN RAISE_APPLICATION_ERROR( -20001, 'Change date must be in the past' ); END IF; END; 

Answer 2

In 11g, you can use control restrictions using sysdate: http://rwijk.blogspot.com/2007/12/check-constraints-with-sysdate.html

Before 11g you should use the Justin approach.

Yours faithfully,
Rob

Answer 3

 CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate) 

This would be really problematic as a limitation. Think about what happens if you need to update a row (some other column) or deactivate / respond to a constraint. It will not check the original date, but against current SYSDATE!

I would recommend adding another column to the table, the default is SYSDATE, and create a constraint or TRIGGER that will compare the new column (saved by SYSDATE) with change_date.

Answer 4

I think you can define a user-defined function that performs this check and use it in a control constraint.