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