How to prevent entering value in table when primary is used on other table?

Question

I have to construct and populate a supertype-subtype relationship, but I cannot get it to work the way it is supposed to be. Basically PERSON table is the supertype of table STUDENT and TEACHER(subtypes). But a person can be either a student or a teacher.

Attributes:

PERSON(p_id, name, dob)

STUDENT (s_id, p_id, grade)

TEACHER(t_id, p_id, tel)

---

Both student and teacher should have names and DOB's along with the p_id as a foreign key, but if it exist on one table it shouldn't be on the other

CREATE TABLE PERSON ( -- SUPERTYPE
    p_id NUMBER(2) CONSTRAINT c1 PRIMARY KEY,
    name CHAR(15),
    dob DATE
);

CREATE TABLE STUDENT ( -- SUBTYPE
    s_id NUMBER(2) CONSTRAINT c2 PRIMARY KEY,
    p_id_fk,
    grade CHAR(1),
    FOREIGN KEY (p_id_fk) REFERENCING PERSON (p_id)
);

CREATE TABLE TEACHER( -- SUBTYPE
    t_id NUMBER(4) CONSTRAINT c3 PRIMARY KEY,
    p_id_fk,
    tel CHAR(8),
    FOREIGN KEY (p_id_fk) REFERENCING PERSON (p_id)
);

INSERT INTO PERSON VALUES (11, 'John', to_date('12/12/12', 'dd/mm/yy'));
INSERT INTO PERSON VALUES (22, 'Maria', to_date('01/01/01', 'dd/mm/yy'));
INSERT INTO PERSON VALUES (33, 'Philip', to_date('02/02/02', 'dd/mm/yy'));

INSERT INTO STUDENT VALUES (98, 11, 'A');

INSERT INTO TEACHER VALUES (1234, 11, 14809510);

How to prevent Person 11 (John) from existing in both tables?

Answer 1

One option is to use database triggers, one for each table (STUDENT and TEACHER); they look the same:

Trigger on STUDENT:

SQL> create or replace trigger trg_bi_stu
  2    before insert on student
  3    for each row
  4  declare
  5    l_cnt number;
  6  begin
  7    -- inserting into STUDENT: check whether that person exists in TEACHER table
  8    select count(*)
  9      into l_cnt
 10      from teacher
 11      where p_id_fk = :new.p_id_fk;
 12
 13    if l_cnt > 0 then
 14       raise_application_error(-20001, 'That person is a teacher; can not be a student');
 15    end if;
 16  end;
 17  /

Trigger created.

Trigger on TEACHER:

SQL> create or replace trigger trg_bi_tea
  2    before insert on teacher
  3    for each row
  4  declare
  5    l_cnt number;
  6  begin
  7    -- inserting into TEACHER: check whether that person exists in STUDENT table
  8    select count(*)
  9      into l_cnt
 10      from student
 11      where p_id_fk = :new.p_id_fk;
 12
 13    if l_cnt > 0 then
 14       raise_application_error(-20001, 'That person is a student; can not be a teacher');
 15    end if;
 16  end;
 17  /

Trigger created.

SQL>

Testing:

SQL> INSERT INTO STUDENT VALUES (98, 11, 'A');

1 row created.

SQL>
SQL> INSERT INTO TEACHER VALUES (1234, 11, 14809510);
INSERT INTO TEACHER VALUES (1234, 11, 14809510)
            *
ERROR at line 1:
ORA-20001: That person is a student; can not be a teacher
ORA-06512: at "SCOTT.TRG_BI_TEA", line 11
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_TEA'


SQL>

Answer 2

There exists four ways how to map an inheritance to relational database.

You are using the most exotic third option, which is causing the problem. Consider to switch to one of the other options as a solution.

The first three are well understood and documented, this page provides usefull links.

Basically you can map

1) all classes in one table

2) use one table for all concrete classes

3) define one table for aech class

All this options have some problems either with excesive joins or with deactivated constraints (e.g. you can't define non nullable columns in option 1), so for completness the option 4) is do not use inheritance in relational database.

You have tried to implement the option 3), but the problem is that all tables must inherite the same primary key (to enfornce 1:1 relation) and use this primary key as a foreign key.

Here the overview of all options for your example

-- 1) single table
CREATE TABLE PERSON (  
    p_id NUMBER(2) CONSTRAINT pers_pk PRIMARY KEY,
    name CHAR(15),
    dob DATE,
    grade CHAR(1),
    tel CHAR(8),
    person_type VARCHAR2(10) CONSTRAINT pers_type CHECK  (person_type in ('STUDENT','TEACHER'))
);

-- 2) table per concrete class
CREATE TABLE STUDENT (  
    p_id NUMBER(2) CONSTRAINT stud_pk PRIMARY KEY,
    name CHAR(15),
    dob DATE,
    grade CHAR(1) 
);

CREATE TABLE TEACHER(  
    p_id NUMBER(2) CONSTRAINT tech_pk PRIMARY KEY,
    name CHAR(15),
    dob DATE,
    tel CHAR(8)
);

-- 3) table per class
CREATE TABLE PERSON (  
    p_id NUMBER(2) CONSTRAINT pers_pk PRIMARY KEY,
    name CHAR(15),
    dob DATE
);

CREATE TABLE STUDENT (  
    p_id NUMBER(2) CONSTRAINT stud_pk PRIMARY KEY,
    grade CHAR(1),
    FOREIGN KEY (p_id) REFERENCING PERSON (p_id)
);

CREATE TABLE TEACHER( 
    p_id NUMBER(2) CONSTRAINT tech_pk PRIMARY KEY,
    tel CHAR(8),
    FOREIGN KEY (p_id) REFERENCING PERSON (p_id)
);

INSERT INTO PERSON (P_ID, NAME, DOB) VALUES (11, 'John', to_date('12/12/2012', 'dd/mm/yyyy'));
INSERT INTO PERSON (P_ID, NAME, DOB) VALUES (22, 'Maria', to_date('01/01/2001', 'dd/mm/yyyy'));
INSERT INTO PERSON (P_ID, NAME, DOB) VALUES (33, 'Philip', to_date('02/02/2002', 'dd/mm/yyyy'));

INSERT INTO STUDENT (P_ID, GRADE) VALUES (11, 'A');
INSERT INTO TEACHER (P_ID, TEL) VALUES (11, 14809510);

Answer 3

Use a materialized view with an appropriate constraint to check complex requirements such as this.

For example, let's create the tables and a materialized view, add data to the tables, and refresh the MV:

CREATE TABLE PERSON ( -- SUPERTYPE
    p_id NUMBER(2) CONSTRAINT c1 PRIMARY KEY,
    name CHAR(15),
    dob DATE
);

CREATE TABLE STUDENT ( -- SUBTYPE
    s_id NUMBER(2) CONSTRAINT c2 PRIMARY KEY,
    p_id_fk,
    grade CHAR(1),
    FOREIGN KEY (p_id_fk) REFERENCING PERSON (p_id)
);

CREATE TABLE TEACHER( -- SUBTYPE
    t_id NUMBER(4) CONSTRAINT c3 PRIMARY KEY,
    p_id_fk,
    tel CHAR(8),
    FOREIGN KEY (p_id_fk) REFERENCING PERSON (p_id)
);

CREATE MATERIALIZED VIEW PERSON_MV
  REFRESH COMPLETE
  AS SELECT p.P_ID,
            s.S_ID,
            t.T_ID
       FROM PERSON p
       LEFT OUTER JOIN STUDENT s
         ON s.P_ID_FK = p.P_ID
       LEFT OUTER JOIN TEACHER t
         ON t.P_ID_FK = p.P_ID;

-- Add constraint to the table underlying the MV

ALTER MATERIALIZED VIEW PERSON_MV
  ADD CONSTRAINT PERSON_MV_CK1
    CHECK( (S_ID IS NULL AND       -- either both are NULL
            T_ID IS NULL) OR
           ( (S_ID IS NULL OR      -- or only one is NULL
              T_ID IS NULL) AND
             (S_ID IS NOT NULL OR
              T_ID IS NOT NULL)));

INSERT ALL
  INTO PERSON (P_ID, NAME, DOB) VALUES (11, 'John', to_date('12/12/2012', 'dd/mm/yyyy'))
  INTO PERSON (P_ID, NAME, DOB) VALUES (22, 'Maria', to_date('01/01/2001', 'dd/mm/yyyy'))
  INTO PERSON (P_ID, NAME, DOB) VALUES (33, 'Philip', to_date('02/02/2002', 'dd/mm/yyyy'))
SELECT * FROM DUAL;

COMMIT;

INSERT INTO STUDENT VALUES (98, 11, 'A');

COMMIT;

BEGIN
  DBMS_MVIEW.REFRESH('PERSON_MV', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE, FALSE);
END;
/

SELECT *
  FROM PERSON_MV;

Note the constraint added to the materialized view:

ALTER MATERIALIZED VIEW PERSON_MV
  ADD CONSTRAINT PERSON_MV_CK1
    CHECK( (S_ID IS NULL AND       -- either both are NULL
            T_ID IS NULL) OR
           ( (S_ID IS NULL OR      -- or only one is NULL
              T_ID IS NULL) AND
             (S_ID IS NOT NULL OR
              T_ID IS NOT NULL)));

This constraint allows data to exist where:

• a PERSON row exists, but neither a related STUDENT or TEACHER row exists
• a PERSON row exists along with either a related STUDENT or TEACHER row, but not both

So when we execute the final SELECT from the materialized view we get:

P_ID  S_ID  T_ID
11     98    - 
33     -     - 
22     -     - 

Now let's modify the script above, adding the following just after the INSERT INTO STUDENT:

INSERT INTO TEACHER VALUES (1234, 11, 14809510);

COMMIT;

If we re-run the entire script, we find that when DBMS_MVIEW.REFRESH is called to refresh the materialized view we get:

ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3012
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2424
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2405
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2968
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3255
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3287
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

This is Oracle's rather long-winded way to say that the constraint was violated.

See this LiveSQL Oracle session

Answer 4

Following on from @Bob's materialized view approach, a version that catches the issue on commit might look like this:

CREATE MATERIALIZED VIEW LOG ON STUDENT WITH PRIMARY KEY, ROWID;

CREATE MATERIALIZED VIEW LOG ON TEACHER WITH PRIMARY KEY, ROWID;

CREATE MATERIALIZED VIEW PERSON_HACK (p_id_fk, marker, rid) 
BUILD IMMEDIATE 
REFRESH ON COMMIT AS 
SELECT p_id_fk, 1, ROWID FROM STUDENT 
UNION ALL 
SELECT p_id_fk, 2, ROWID FROM TEACHER;

ALTER MATERIALIZED VIEW PERSON_HACK 
ADD CONSTRAINT PERSON_HACK_PK PRIMARY KEY (p_id_fk);

That should act similarly to a deferred constraint, erroring when the MV is refreshed (and its primary key violated) on commit. With concurrent inserts the second session to commit would see the error.

Live SQL, though it seems to have some issues with MVs (reported elsewhere). It should report the constraint violation, not throw ORA-12008. But I don't currently have access to test elsewhere - neither SQL Fiddle or db<>fiddle allow MVs to be created.

---

Of course, if you haven't been taught about MVs yet then using one in an assignment might look a bit odd. It seems a bit more likely that you've been taught about objects and the assignment is expecting those - even though they are very rarely used in the real world (within the DB), they seem to be taught anyway, along with old join syntax and other bad practices...