Usando la data in un vincolo di controllo, Oracle

Sto cercando di verificare aggiungere il seguente vincolo ma Oracle restituisce l’errore mostrato di seguito.

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

Errore:

 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. 

Sfortunatamente, un vincolo di controllo non può fare riferimento a una funzione come SYSDATE. Dovresti creare un trigger che verifichi questi valori quando si verifica DML, es

 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; 

Non è ansible utilizzare SYSDATE nel vincolo di controllo. Secondo la documentazione

Le condizioni dei vincoli di controllo non possono contenere i seguenti costrutti:

  • Sottoquery ed espressioni sottoquery scalari
  • Chiama le funzioni che non sono deterministiche (CURRENT_DATE,
    CURRENT_TIMESTAMP, DBTIMEZONE,
    LOCALTIMESTAMP, SESSIONTIMEZONE,
    SYSDATE , SYSTIMESTAMP, UID, USER e USERENV)
  • Chiamate alle funzioni definite dall’utente
  • Dereferenziazione delle colonne REF (ad esempio, utilizzando la funzione DEREF)
  • Colonne o attributi della tabella nidificati
  • Le pseudocolonne CURRVAL, NEXTVAL, LEVEL o ROWNUM
  • Costanti di data che non sono completamente specificate

Per 10g Release 2 (10.2) , vedere constraint, e per 11g Release 2 (11.2) vedere constraint .

Ricorda che un vincolo di integrità è un’asserzione sui dati della tabella che è sempre vera.

Ad ogni modo: non so esattamente cosa stai cercando di ottenere, ma penso che tu possa usare i grilletti per questo scopo.

Ogni volta che il record viene aggiornato, SYSDATE avrà un valore diverso. Pertanto il vincolo verrà convalidato in modo diverso ogni volta. Oracle non consente sysdate in un vincolo per questo motivo.

È ansible risolvere il problema con un trigger che verifica se CloseDate è effettivamente cambiato e genera un’eccezione quando il nuovo valore non è compreso nell’intervallo.

E: Che cos’è (StartDate > (CloseDate + (SYSDATE + 730)))) ? Non puoi aggiungere date.

E: StartDate deve essere dopo CloseDate ? Non è strano?

Scrivi sysdate in una colonna e usalo per la convalida. Questa colonna potrebbe essere la colonna di controllo (Ad esempio: data di creazione)

 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 

Puoi ottenerlo quando fai un piccolo trucco come questo:

 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 ); 

Naturalmente, la funzione SYSDATE_DETERMINISTIC non è deterministica, ma Oracle consente comunque di dichiararla.

Forse nelle versioni future Oracle diventa più intelligente e non permetterà più tali trucchi.

Non consiglio di cantare triggers come vincolo e di sollevare eccezioni, ma puoi usare una colonna per memorizzare SYSDATE come data di registrazione (se già lo hai, puoi usarlo) e poi il tuo vincolo confronta questa colonna invece di 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))));