Как сделать так, чтобы два столбца внешнего ключа были либо равны нулю, либо оба указывали на какую-то строку в другой таблице в Oracle?

Question

У меня есть две таблицы, "Сотрудники" и "Задачи" (это, конечно, не настоящий код SQL, перечислены только важные вещи):

CREATE TABLE Employees (
    employee_id      NUMBER(6) NOT NULL,
    is_boss          NUMBER(1) DEFAULT 0 NOT NULL,
    name             VARCHAR2(32) NOT NULL,

    CHECK (is_boss IN (0,1)),
    UNIQUE (is_boss, employee_id)
);

CREATE TABLE Tasks (
    task_id          NUMBER(6) NOT NULL,
    name             VARCHAR2(32) NOT NULL,
    is_boss          NUMBER(1),
    employee_id      NUMBER(6),
    finish_date      DATE,

    CHECK (is_boss IN (1)),
    FOREIGN KEY (employee_id) REFERENCES Employees (employee_id),
    FOREIGN KEY (is_boss) REFERENCES Employees (is_boss)
);

Таким образом, таблица "Задачи" содержит несколько задач. Когда они добавляются в таблицу, нам нужны только имя и идентификатор, поэтому другие поля обнуляются. В какой-то момент времени каждое задание должно быть подтверждено "начальником", поэтому сотрудник, который имеет is_boss == 1 и только потом finish_date ставится, довольно просто.

Эти проверочные ограничения и внешние / уникальные ключи работают хорошо, если задача обновляется обоими is_boss а также employee_id - если работник не начальник, выдается ошибка, если такого сотрудника тоже нет. Но если один из них нулевой, все идет не так. Итак, я хочу как-то принудить базу данных проверить, чтобы эти два поля были либо не нулевыми, либо обоими нулевыми. Собственно хочу 3 поля (finish_date тоже) быть либо нулевым, либо совсем не нулевым.

Триггер, вероятно, вариант, но мой учитель базы данных очень против использования их, если есть другая, более простая возможность. Итак, мой вопрос - есть ли способ обеспечить его без триггера? СУБД Oracle 11g.

Заранее спасибо.

Answer 1

Вам нужно объединить два внешних ключа в один внешний ключ - в противном случае, я думаю, вы обнаружите, что они делают не совсем то, что, как вы думаете, они делают. Кроме того, вам необходимо проверить ограничение, чтобы убедиться, что все три поля установлены или все три имеют значение NULL. Ваша таблица TASKS должна выглядеть примерно так:

CREATE TABLE TASKS (
    TASK_ID          NUMBER(6) NOT NULL,
    NAME             VARCHAR2(32) NOT NULL,
    IS_BOSS          NUMBER(1),
    EMPLOYEE_ID      NUMBER(6),
    FINISH_DATE      DATE,

    CONSTRAINT TASKS_CK1
      CHECK (is_boss IN (1)),
    CONSTRAINT TASKS_FK1
      FOREIGN KEY (IS_BOSS, EMPLOYEE_ID)
        REFERENCES EMPLOYEES (IS_BOSS, EMPLOYEE_ID),
    CONSTRAINT TASKS_CK2
      CHECK((IS_BOSS IS NULL AND
               EMPLOYEE_ID IS NULL AND
               FINISH_DATE IS NULL)
            OR
            (IS_BOSS IS NOT NULL AND
               EMPLOYEE_ID IS NOT NULL AND
               FINISH_DATE IS NOT NULL))
);