We cannot use SYSDATE as part of a CHECK Constraint defination.
See the example:
SQL> CREATE TABLE EVENT(
2 EVENT_ID NUMBER(5) CONSTRAINT PK_EVENT PRIMARY KEY,
3 EVENT_NAME VARCHAR2(25),
4 VENUE VARCHAR2(20),
5 START_DATE DATE CONSTRAINT CK_EVENT_START_DATE CHECK
(START_DATEAction: Completely specify the date constant or system variable.
Reason:
Note that Oracle7 is lax on DATE check constraints and this has been tightened up in Oracle8 such that a CHECK CONSTRAINT for a date column needs a fully qualified 4-digit year date and a to_date clause. This is because the interpretation of the check constraint could be changed by either a change in century or a change in NLS_DATE_FORMAT.
Eg: ALTER TABLE xxx ADD CONSTRAINT yyy
CHECK ( datcol BETWEEN ‘01-jan-1998′ and ‘01-feb-1998′ );
depends on the current setting of NLS_DATE_FORMAT. One should use
CHECK ( datcol BETWEEN to_date(’01-jan-1998′,’dd-mon-yyyy’)
and to_date(’01-feb-1998′,’dd-mon-yyyy’);
Solution:
“The condition of a CHECK constraint has the following limitations:
. The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.
. The condition cannot contain subqueries or sequences. The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions.
. The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM.
. The condition cannot contain a user-defined SQL function.”
Thus, a trigger seems to me to be a good choice here.
1 response so far ↓
1 Rajender Singh // Jan 30, 2007 at 12:40 pm
Intersting Observation!
Leave a Comment