Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anwar.

Asked: July 21, 2000 - 9:07 am UTC

Last updated: July 21, 2000 - 9:07 am UTC

Version: 7.3.2

Viewed 1000+ times

You Asked

Hi Tom,

I've seen nemerous examples quoting the use of 'sysdate' in database check constraints but when I tried to do so an error was returned? What is the problem with it?

and Tom said...

Its never been allowed:


ops$tkyte@DEV8I.WORLD> create table t ( x date check ( x > sysdate ) );
create table t ( x date check ( x > sysdate ) )
*
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint


$ oerr ora 2436
02436, 00000, "date or system variable wrongly specified in CHECK constraint"

*Cause:
An attempt was made to use a date constant or system variable,
such as USER, in a check constraint that was not completely
specified in a CREATE TABLE or ALTER TABLE statement. For
example, a date was specified without the century.

*Action:
Completely specify the date constant or system variable.

Can you point me to an example in the Oracle documentation that shows otherwise?


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SYSDATE / USER in Constraints

The Brad, July 27, 2007 - 10:03 am UTC 

Just create an additional column using as default SYSDATE. Then you can use this column to compare. And if you want to hide this column create a view around it...

SQL> create table t
  2  (x   date,
  3   y   date default sysdate,
  4   constraint x_y check
  5   (
  6      x >= y
  7   )
  8  );

Table created.

SQL> 
SQL> create view v as select x from t;

View created.

SQL> 
SQL> insert into v values (sysdate);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> insert into v values (sysdate-1);
insert into v values (sysdate-1)
*
ERROR at line 1:
ORA-02290: check constraint (ADBM.X_Y) violated


SQL>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.