CODEWITHSUNDEEP
sqldatabaseoracle-databaseforeign-keysdata-integrity
paolostyle
paolostyle

Reputation: 1988

How to enforce two foreign key columns to be either both null or both pointing to some row in other table in Oracle?

I have two tables, Employees and Tasks (this is not an actual SQL code of course, listed just important stuff):

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

So the Tasks table contains some tasks. When they are added to the table we only need the name and id, that's why other fields are nullable. At some point in time every task has to be confirmed by a "boss", so an employee which has is_boss == 1 and only then finish_date is put, quite simple.

Those check constraints and foreign/unique keys work well if a task is updated with both is_boss and employee_id - if an employee is not a boss, it throws an error, if there isn't such employee also. But if one of those is null everything goes wrong. So what I want is to somehow enforce the database to check to have those two fields be either both not null or both null. Actually, I want 3 fields (finish_date too) to be either all null or all not null.

A trigger is probably an option, but my database teacher is very much against using them if there is a different, simpler possibility. So my question is - is there a way to enforce it without a trigger? DBMS is Oracle 11g.

Thanks in advance.

Upvotes: 0

Views: 237

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

You need to combine your two foreign keys into a single foreign key - otherwise I think you'll find they're not doing quite what you think they're doing. Also, you need a check constraint to ensure that all three fields are set or all three are NULL. Your TASKS table needs to be something like:

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

Upvotes: 1

Related Questions