Foreign key which accepts only selected values

Question

Say I have following tables:

EMPLOYEE : 
    ID(PrimaryKey) | NAME | DESIGNATION

Let's say DESIGNATION can have these values:

MANAGER
ENGINEER
etc etc..

MANAGERS_DETAILS 

    ID | NAME | SALARY

ID in MANAGERS_DETAILS is Foreign Key of EMPLOYEE table ID.i.e,

CONSTRAINT managers_details$fk1 FOREIGN KEY (ID) 
    REFERENCES EMPLOYEE(ID) ON DELETE CASCADE

Is there anyway to restrict inserting the employees who are not MANAGERS? i.e. insert into the table only when designation employee is MANAGER?

Hi. This is a faq. The topic is sql/database sutyping/inheritance. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. — philipxy, Sep 06 '18 at 00:10
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Sep 06 '18 at 00:11

MT0 · Answer 1 · 2018-09-05T09:18:13.943

2

You can add a virtual column to the EMPLOYEE table that contains the id if the person is a manager and add a UNIQUE constraint to that virtual column. Then you can reference that as the FOREIGN KEY:

CREATE TABLE employee (
  id          NUMBER(10,0) PRIMARY KEY,
  name        VARCHAR2(100),
  designation VARCHAR2(10),
  idIfManager NUMBER(10,0) GENERATED ALWAYS AS (
                  CASE DESIGNATION WHEN 'MANAGER' THEN id END
                ) VIRTUAL
                CONSTRAINT employee__idIfManager__u UNIQUE
)
/

CREATE TABLE manager_details (
  id     NUMBER(10,0)
         PRIMARY KEY
         REFERENCES employee ( idIfManager ),
  name   VARCHAR2(100),
  salary NUMBER(12,2)
)
/

edited Sep 05 '18 at 09:18

answered Sep 05 '18 at 08:59

MT0

143,790
11
59
117

If you're on an older version of oracle you can also use a trigger for this task. But this should be the way to go otherwise! – Radagast81 Sep 05 '18 at 09:57
@MT0 y do we need `UNIQUE` constraint for VIRTUAL column ? – ihm017 Sep 05 '18 at 11:12
@ihm017 A `FOREIGN KEY` constraint must reference column(s) constrained by either a `PRIMARY KEY` or `UNIQUE` constraint. Since the virtual column contains `NULL` values it cannot be a `PRIMARY KEY` constraint so it must be a `UNIQUE` constraint. – MT0 Sep 05 '18 at 11:15
@MT0 You also have already a `PRIMARY KEY` on the table and there can only be one. @ihm017 On the otherhand the uniqueness of the virtual column is already garanteed by this `PRIMARY KEY` so you don't have to worry about the `UNIQUE KEY` - only perhaps the addtional disk space... – Radagast81 Sep 05 '18 at 11:27
@Radagast81 Yes, the uniqueness is guaranteed by the underlying `PRIMARY KEY` the virtual column references - however to reference a column with a `FOREIGN KEY` you must have either a `PRIMARY KEY` or `UNIQUE` constraint on that referenced column so the `UNIQUE` key is not optional and is required for this to work. Try it with the `UNIQUE` constraint commented out and you will get `ORA-02270: no matching unique or primary key for this column-list` when you try to create the `manager_details` table. – MT0 Sep 05 '18 at 11:39

APC · Answer 2 · 2018-09-05T13:10:00.957

There isn't any way to enforce a SQL ASSERTION in Oracle. However, you could fudge like this:

 alter table manager_details add designation varchar2(7) default 'MANAGER' not null;
 alter table manager_details add constraint md_mgr_ck check (designation = 'MANAGER');

Now add another constraint to EMPLOYEE:

alter table employee add constraint emp_uk unique (id, designation);

Now you can change the foreign key to be:

CONSTRAINT managers_details$fk1 FOREIGN KEY (ID, designation) 
     REFERENCES EMPLOYEE(ID, designation);

This is complicated but it does means records in MANAGER_DETAILS must be defined as 'MANAGER' in EMPLOYEE.

ihm017 · Answer 3 · 2018-09-05T11:06:12.870

You can use trigger to avoid insertion of data into Manager table if an employee's designation is not manager. You may want to execute this trigger on both Insertion and Updation of the records in Managers table.

CREATE OR REPLACE TRIGGER manager_validation 
BEFORE INSERT OR UPDATE 
ON MANAGERS_DETAILS
FOR EACH ROW 
DECLARE
  desig VARCHAR2(10);
BEGIN

  SELECT designation
    INTO desig
    FROM employee
   WHERE id = :new.id;

  IF (desig != 'MANAGER') THEN 
    raise_application_error( -20001, 'Only Employees who are Managers can be inserted into table.');
  END IF; 

END;

Below is my working example:

-- Create Employee Table
CREATE TABLE employee_ihm
  (
    id          NUMBER PRIMARY KEY,
    name        VARCHAR2(1),
    designation VARCHAR2(10)
  );

-- Create Managers table
CREATE TABLE managers
  (
    id     NUMBER REFERENCES employee_ihm(id) ON DELETE CASCADE,
    name   VARCHAR2(1),
    salary NUMBER
  );


-- Create trigger on insert and update on Managers table
CREATE OR REPLACE TRIGGER manager_validation BEFORE
  INSERT OR UPDATE
  ON managers 
  FOR EACH ROW 
  DECLARE
    desig VARCHAR2(10);
  BEGIN
    SELECT designation INTO desig FROM employee_ihm WHERE id = :new.id;
    IF (desig != 'MANAGER') THEN
      raise_application_error( -20001, 'Only Employees who are Managers can be inserted into table.');
    END IF;
  END;

INSERT INTO employee_ihm VALUES
  (1, 'A', 'MANAGER');
INSERT INTO employee_ihm VALUES
  (2, 'B', 'EMP');
INSERT INTO employee_ihm VALUES
  (3, 'C', 'MANAGER');
INSERT INTO employee_ihm VALUES
  (4, 'D', 'TEAMLEAD');
INSERT INTO employee_ihm VALUES
  (5, 'E', 'TEAMMEM');
INSERT INTO employee_ihm VALUES
  (6, 'F', 'MANAGER');

-- Employee id 1 will be inserted successfully
INSERT INTO managers VALUES
  (1, 'A', 200);

-- Employee id 2 will fail to get inserted into Managers
INSERT INTO managers VALUES
  (2, 'B', 300);

Let me know if this solution works.

I just had the same answer posted, but decidet to delete it as the solution with the virtual column is the by far better approach... For instance it also works, if the data in table employee changes, for which another trigger would be needed otherwise. — Radagast81, Sep 05 '18 at 11:05
yes .. seems to be a concise approach, but my solution will work for earlier versions of Oracle and can be implemented on other DB's as well .. — ihm017, Sep 05 '18 at 11:13

Foreign key which accepts only selected values

3 Answers3