3

I'm created a table called Project with two columns ProjectID and StartDate. I would like to create a constraint that will assure that the date entred is always equal to today or a future date.

I have tried the code below but gets a missing expression error. The Constraint is only added after the creation of the table.

    CREATE TABLE  PROJECT
    (ProjectID NUMBER(7), 
    StartDate date NOT NULL, 
    CONSTRAINT PK_PROJECTID PRIMARY KEY (ProjectID)
    );

    ALTER TABLE PROJECT
    ADD CONSTRAINT PROJECT_Check_StartDate CHECK(StartDate => Current_date);
halfer
  • 19,824
  • 17
  • 99
  • 186
ShoeraB
  • 47
  • 2

1 Answers1

11

Your proximal error is that the correct operator is >=, not =>.

Fixing that problem will only generate a more existential error:

ORA-02436: date or system variable wrongly specified in CHECK constraint

That is, you cannot refer to a dynamic value in a check constraint, and the current date/time is such a value.

The simplest option is to use a trigger to enforce the constraint. A "kind-of" crazy option is to store the create date of the record (which is a good idea) and then store the future portion as another column:

create table . . . (
    daysInTheFuture number,  -- fractional days
    CreatedAt date default sysdate,
    constraint chk_t_number check (daysInTheFuture >= 0)
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786