Usando la fecha en una restricción de verificación, Oracle

Estoy tratando de verificar agregue la siguiente restricción pero Oracle devuelve el error que se muestra a continuación.

ALTER TABLE Table1 ADD (CONSTRAINT GT_Table1_CloseDate CHECK (CloseDate > SYSDATE), CONSTRAINT LT_Table1_CloseDate CHECK (CloseDate  (CloseDate + (SYSDATE + 730)))); 

Error:

 Error report: SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint 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. Setting the event 10149 allows constraints like "a1 > '10-MAY-96'", which a bug permitted to be created before version 8. 

Una restricción de verificación, desafortunadamente, no puede hacer referencia a una función como SYSDATE. Debería crear un disparador que verifica estos valores cuando ocurre DML, es decir,

 CREATE OR REPLACE TRIGGER trg_check_dates BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW BEGIN IF( :new.CloseDate <= SYSDATE ) THEN RAISE_APPLICATION_ERROR( -20001, 'Invalid CloseDate: CloseDate must be greater than the current date - value = ' || to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) ); END IF; IF( :new.CloseDate > add_months(SYSDATE,12) ) THEN RAISE_APPLICATION_ERROR( -20002, 'Invalid CloseDate: CloseDate must be within the next year - value = ' || to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) ); END IF; IF( :new.StartDate <= add_months(:new.CloseDate,24) ) THEN RAISE_APPLICATION_ERROR( -20002, 'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' || to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) || ' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) ); END IF; END; 

No puede usar SYSDATE en la restricción de verificación. De acuerdo con la documentación

Las condiciones de las restricciones de verificación no pueden contener las siguientes construcciones:

  • Subconsultas y expresiones de subconsulta escalar
  • Llamadas a las funciones que no son deterministas (CURRENT_DATE,
    CURRENT_TIMESTAMP, DBTIMEZONE,
    LOCALTIMESTAMP, SESSIONTIMEZONE,
    SYSDATE , SYSTIMESTAMP, UID, USEREN USERENV)
  • Llamadas a funciones definidas por el usuario
  • Desreferencia de columnas REF (por ejemplo, usando la función DEREF)
  • Columnas o atributos de tabla nesteds
  • Los pseudocolumnos CURRVAL, NEXTVAL, LEVEL o ROWNUM
  • Constantes de fecha que no están completamente especificadas

Para 10g Release 2 (10.2) , vea la restricción, y para 11g Release 2 (11.2) vea la restricción .

Recuerde que una restricción de integridad es una statement sobre datos de tabla que siempre es verdadera.

De todos modos: no sé exactamente lo que estás tratando de lograr, pero creo que puedes usar desencadenadores para este fin.

Cada vez que se actualiza el registro, SYSDATE tendrá un valor diferente. Por lo tanto, la restricción se validará de manera diferente cada vez. Oracle no permite sysdate en una restricción por ese motivo.

Es posible que pueda resolver su problema con un desencadenador que compruebe si CloseDate realmente ha cambiado y generar una excepción cuando el nuevo valor no se encuentre dentro del scope.

Y: ¿Qué es (StartDate > (CloseDate + (SYSDATE + 730)))) ? No puedes agregar fechas.

Y: ¿ StartDate debe ser posterior a CloseDate ? ¿No es extraño?

Escriba sysdate en una columna y úselo para validación. Esta columna puede ser su columna de auditoría (p. Ej .: fecha de creación)

 CREATE TABLE "AB_EMPLOYEE22" ( "NAME" VARCHAR2 ( 20 BYTE ), "AGE" NUMBER, "SALARY" NUMBER, "DOB" DATE, "DOJ" DATE DEFAULT SYSDATE ); Table Created ALTER TABLE "AB_EMPLOYEE22" ADD CONSTRAINT AGE_CHECK CHECK((ROUND((DOJ-DOB)/365)) = AGE) ENABLE; Table Altered 

Puedes lograr esto cuando haces un poco de trampa así:

 CREATE OR REPLACE FUNCTION SYSDATE_DETERMINISTIC RETURN DATE DETERMINISTIC IS BEGIN RETURN SYSDATE; END SYSDATE_DETERMINISTIC; / CREATE TABLE Table1 ( s_date DATE, C_DATE DATE GENERATED ALWAYS AS ( SYSDATE_DETERMINISTIC() ) ); ALTER TABLE Table1 ADD CONSTRAINT s_check CHECK ( s_date < C_DATE ); 

Por supuesto, la función SYSDATE_DETERMINISTIC no es determinista, pero Oracle permite declarar esto de todos modos.

Tal vez en versiones futuras, Oracle se vuelva más inteligente y ya no permita tales trucos.

No recomiendo cantar desencadenantes como restricción y levantar excepciones. En su lugar, puede usar una columna para almacenar SYSDATE como fecha de registro (si ya la tiene, puede usarla) y luego su restricción compara esta columna en lugar de SYSDATE.

  ALTER TABLE Table1 ADD (REGISTER_DATE DATE); CREATE OR REPLACE TRIGGER trg_check_dates BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW BEGIN :new.REGISTER_DATE := SYSDATE; END; ALTER TABLE Table1 ADD (CONSTRAINT GT_Table1_CloseDate CHECK (CloseDate > REGISTER_DATE), CONSTRAINT LT_Table1_CloseDate CHECK (CloseDate <= REGISTER_DATE + 365)), CONSTRAINT GT_Table1_StartDate CHECK (StartDate > (CloseDate + (REGISTER_DATE + 730)))); 
    Intereting Posts