Блог

Я пытаюсь проверить добавление следующего ограничения, но Oracle возвращает ошибку, показанную ниже.

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

Ошибка:

 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.
 

Контрольное ограничение, к сожалению, не может ссылаться на такую функцию, как SYSDATE. Вам нужно будет создать триггер, который проверял бы эти значения при возникновении DML, т. Е.

 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;
 

Вы не можете использовать SYSDATE в ограничении проверки. Согласно документации

Условия контрольных ограничений не могут содержать следующие конструкции:

• Подзапросы и скалярные выражения подзапросов
• Вызовы функций, которые не являются детерминированными (CURRENT_DATE,
  CURRENT_TIMESTAMP, DBTIMEZONE,
  LOCALTIMESTAMP, SESSIONTIMEZONE,
  SYSDATE, СИСТЕМНАЯ МЕТКА, UID, ПОЛЬЗОВАТЕЛЬ и ИМЯ ПОЛЬЗОВАТЕЛЯ)
• Вызовы пользовательских функций
• Разыменование столбцов ссылок (например, с помощью функции DEREF)
• Столбцы или атрибуты вложенной таблицы
• Псевдоколонны CURRVAL, NEXTVAL, LEVEL или ROWNUM
• Константы даты, которые не указаны полностью

Для выпуска 10g 2 (10.2) см. Ограничение, а для выпуска 11g 2 (11.2) см. Ограничение.

Помните, что ограничение целостности-это утверждение о табличных данных, которое всегда верно.

В любом случае: я не знаю точно, чего вы пытаетесь достичь, но я думаю, что вы можете использовать триггеры для этой цели.

Каждый раз, когда запись обновляется, SYSDATE будет иметь другое значение. Поэтому ограничение будет проверяться каждый раз по-разному. По этой причине Oracle не разрешает sysdate в ограничении.

Возможно, вы сможете решить свою проблему с помощью триггера, который проверяет, действительно ли изменилось значение CloseDate, и создает исключение, когда новое значение находится вне диапазона.

И: Что такое (StartDate > (CloseDate (SYSDATE 730)))) ? Вы не можете добавлять даты.

И: StartDate нужно ли после CloseDate этого? Разве это не странно?

Запишите sysdate в столбец и используйте его для проверки. Этот столбец может быть вашим столбцом аудита (например, дата создания).

 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
 

Вы можете достичь этого, если совершите небольшой обман, подобный этому:

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

Конечно, функция SYSDATE_DETERMINISTIC не является детерминированной, но Oracle все равно позволяет это объявить.

Возможно, в будущих выпусках Oracle станет более умной и больше не будет допускать подобных трюков.

Я не рекомендую использовать триггеры в качестве ограничения и для создания исключений, вместо этого вы можете использовать столбец для хранения SYSDATE в качестве даты регистрации(если он у вас уже есть, вы можете его использовать), а затем ваше ограничение сравнивает этот столбец вместо 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))));