0

I am creating a table with date column. I would like to add a check constraint to validate if the date is in future.

create table test_table (
  schedule_date date not null,
  check (schedule_date >= TODAY)
);

Above sql gives me a syntax error. 

09:43:37  [CREATE - 0 row(s), 0.000 secs]  [Error Code: -201, SQL State: 42000]  A syntax error has occurred. 
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

How do I add a constraint on date column?

usha
  • 28,973
  • 5
  • 72
  • 93

3 Answers3

4

You cannot use today in the check constraint. As the official documentation states:

Check constraints are defined using search conditions. The search condition cannot contain user-defined routines, subqueries, aggregates, host variables, or rowids. In addition, the condition cannot contain the variant built-in functions CURRENT, USER, SITENAME, DBSERVERNAME, or TODAY.

I think a solution can be using an insert/update trigger.

Copilot
  • 782
  • 6
  • 17
1

As Copilot says, it's simply not permitted by Informix. If any other RDBMS does allow it, I'd be surprised.

The inherent ticking time bomb should be obvious when you think about it. The row when inserted meets the constraint, but will fail the constraint as time passes. How is the engine to flag that?

The solution is either:

  • the application responsible for creating the row to be inserted is responsible for pre-validating it, or
  • create an insert trigger that when fired executes a procedure to validate, and raise the appropriate error if the row fails
RET
  • 9,100
  • 1
  • 28
  • 33
0

i dont have an informix db to check but I think the comma is extraneous and causing your syntax error.

try

create table test_table (
  schedule_date date not null CHECK (schedule_date >= TODAY)
);
David Chan
  • 7,347
  • 1
  • 28
  • 49