4

I'm trying to create a table that records changes made to the estimated hours of another table. The database as a whole is a project management system for a company to assign work to its employees and create invoices for the customer.

Currently I 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 must not be a future date, it must be either today or in the past. The last check constraint is the problem. As I understand it you cannot use the sysdate because of a reason I've forgotten, but you can't. I've also tried GETDATE() and every other variant I've found online. How can I do this presumably simple task?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
solent_matt
  • 41
  • 1
  • 1
  • 3
  • 1
    `GETDATE()` is TSQL specific. `SYSDATE` is PLSQL specific means of getting the current date and time, while `CURRENT_TIMESTAMP` is the ANSI alternative (supported by the majority of databases). Are you actually hitting an error? Because this is the first I've heard about not using SYSDATE, so I'd like to hear more. Your version of Oracle would also help. – OMG Ponies Jan 19 '11 at 18:53

4 Answers4

10

You can't call a function from a check constraint so the most natural approach would be to define a trigger on 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;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
2
CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate)

This would be really problematic as a Constraint. Consider what would happen if one were to update a row (some other column) or deactivate/reactive the constraint. It won't check against the original date, but against the current SYSDATE!

I would advocate adding another column to the table, defaulted to SYSDATE, and building a constraint or TRIGGER that will compare the new column (stored SYSDATE) against change_date.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
2

In 11g, it is possible to use check constraints with sysdate: http://rwijk.blogspot.com/2007/12/check-constraints-with-sysdate.html

Prior to 11g you should use Justin's approach.

Regards,
Rob.

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
-2

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

Antoine Aubry
  • 12,203
  • 10
  • 45
  • 74