À l'aide de la date dans une contrainte check, Oracle

Je suis en train d'ajouter le suivant contrainte, mais Oracle renvoie l'erreur indiqué ci-dessous.

ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > SYSDATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= SYSDATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (SYSDATE + 730))));

Erreur:

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.
InformationsquelleAutor Jon | 2011-03-16
  1. 16

    Une contrainte check, malheureusement, ne peut pas référencer une fonction SYSDATE. Vous devez créer un déclencheur qui a vérifié ces valeurs lors de la DML se produit, c'est à dire

    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;
    InformationsquelleAutor Justin Cave
  2. 14

    Vous ne pouvez pas utiliser SYSDATE dans la vérification de la contrainte. Selon la documentation

    Conditions de contraintes de vérification ne peut pas
    contenir les constructions suivantes:

    • Les sous-requêtes et sous-requête scalaire expressions
    • Les appels aux fonctions qui sont pas déterministe (CURRENT_DATE,

      CURRENT_TIMESTAMP, DBTIMEZONE,

      LOCALTIMESTAMP, SESSIONTIMEZONE,

      SYSDATE, SYSTIMESTAMP, UID de l'UTILISATEUR, et
      USERENV)
    • Des appels à des fonctions définies par l'utilisateur
    • Déréférencement de RÉF colonnes (par exemple, à l'aide de la DEREF fonction)
    • Colonnes de la table imbriquée ou attributs
    • La pseudocolumns CURRVAL, NEXTVAL, le NIVEAU, ou ROWNUM
    • Date de constantes qui ne sont pas entièrement spécifiés

    Pour 10g Release 2 (10.2), voir contrainte, et pour 11g Release 2 (11.2) voir contrainte.

    N'oubliez pas qu'une contrainte d'intégrité est une déclaration sur les données de la table de toujours vrai.

    De toute façon: je ne sais pas exactement ce que vous essayez d'atteindre, mais je pense que vous pouvez utiliser déclenche à cette fin.

    InformationsquelleAutor Marcin Wroblewski
  3. 5

    Écrire sysdate dans une colonne et de les utiliser pour la validation. Cette colonne peut être votre audit de la colonne (Par exemple: date de création)

    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
    InformationsquelleAutor SriniV
  4. 4

    Chaque fois que l'enregistrement est mis à jour SYSDATE aura une valeur différente. Par conséquent, la contrainte de valider de façon différente à chaque fois. Oracle ne permet pas sysdate dans une contrainte pour cette raison.

    Vous serez en mesure de résoudre votre problème avec une gâchette qui vérifie si CloseDate a réellement changé et déclencher une exception lorsque la nouvelle valeur n'est pas dans la gamme.

    Et: qu'est-Ce que (StartDate > (CloseDate + (SYSDATE + 730))))? Vous ne pouvez pas ajouter des dates.

    Et: StartDate doit être après CloseDate? N'est-ce pas bizarre?

    InformationsquelleAutor Jeff
  5. 1

    Vous pouvez atteindre cet objectif lorsque vous faites un peu de tricher, comme ceci:

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

    Bien sûr, la fonction SYSDATE_DETERMINISTIC est pas déterministe, mais Oracle permet de déclarer cela de toute façon.

    Peut-être dans les futures versions d'Oracle devient de plus en plus intelligents et ne permettront pas de tels tours.

    InformationsquelleAutor Wernfried Domscheit
  6. 0

    Je ne recommande pas de chanter les déclencheurs en tant que contrainte et de lever des exceptions, au Lieu de cela, vous pouvez utiliser une colonne pour stocker SYSDATE que la date d'enregistrement(si vous l'avez déjà, alors vous pouvez l'utiliser), puis votre contrainte compare cette colonne au lieu 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))));
    InformationsquelleAutor hmmftg