| ORA-02436:date or system variable wrongly specified in CHECK constraint [message #117699] |
Fri, 29 April 2005 00:52  |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Morning..
How can I impose a check constraint on DATE column. for example I want Date of Birth to be always before SYSDATE. I tried the following, but here is an error
plz suggest a solution.
SQL> alter table emp add constraint cc_emp_dob CHECK(dob < sysdate);
alter table emp add constraint cc_emp_dob CHECK(dob < sysdate)
*
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint
thanks
Khan
|
|
|
|
| Re: ORA-02436:date or system variable wrongly specified in CHECK constraint [message #117707 is a reply to message #117699] |
Fri, 29 April 2005 01:58  |
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
|
Member |
|
|
Hi Khan,
Unfortunately you can't use SYSDATE in a check constraint as it is a dynamic Oracle variable. A better way to enforce this constraint would be to use a trigger. For example:
CREATE OR REPLACE TRIGGER trg_emp_dob BEFORE INSERT OR UPDATE ON emp
BEGIN
IF :new.dob > SYSDATE THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => 'DOB Cannot be in the future');
END IF;
END;
Cheers
Andy
|
|
|
|
| Re: ORA-02436:date or system variable wrongly specified in CHECK constraint [message #117770 is a reply to message #117699] |
Fri, 29 April 2005 09:20 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Hey just curious, but could you create a date table that contains all dates prior to current date, and have a reference constraint from your column to that table?
Then each day you would have a job that adds the next day to your date table. You probably wouldn't have to keep it completely up to date depending on the age of people you are dealing with.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
