I'm trying to implement a table with typed relations. By that I mean a relation is conditional and requires extra validation. To demo this I use a PERSON table where each person has an ID, Name, Gender and foreign keys to FatherID and MotherID.
What I want to enforce is:
- FATHERID must point to a PERSON WHERE GENDER='M'
- MOTHERID must point to a PERSON WHERE GENDER='F'
- You cannot change the gender of a person if other records reference it via FatherID or MotherID
CREATE TABLE "PERSON"
"ID" NUMBER(9,0) NOT NULL
, "NAME" VARCHAR2(30) NOT NULL
, "GENDER" VARCHAR2(1) NOT NULL
, "FATHERID" NUMBER(9,0) NULL
, "MOTHERID" Number(9,0) NULL
, CONSTRAINT "CK_PERSON_GENDER" CHECK ("GENDER" IN ('M', 'F'))
, CONSTRAINT "PK_PERSON" PRIMARY KEY ("ID")
, CONSTRAINT "FK_PERSON_FATHER" FOREIGN KEY ("FATHERID") REFERENCES "PERSON"("ID") ON DELETE CASCADE USING INDEX (CREATE INDEX "FK_PERSON_FATHER" ON "PERSON"("FATHERID") TABLESPACE "MYSPACE")
, CONSTRAINT "FK_PERSON_MOTHER" FOREIGN KEY ("MOTHERID") REFERENCES "PERSON"("ID") ON DELETE CASCADE USING INDEX (CREATE INDEX "FK_PERSON_MOTHER" ON "PERSON"("MOTHERID") TABLESPACE "MYSPACE")
) TABLESPACE "MYSPACE";
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
(so I omitted the FOREIGN KEY constraints and did the same with other statements but I cannot see what is wrong with the above)
I want to enforce the typed relations and tried to add this materialized view:
create materialized view "PERS_MV"
refresh fast on commit
as select c.id
from person c join person p1 on c.fatherid = p1.id
join person p2 on c.motherid = p2.id
where p1.gender = 'F' or p2.gender = 'M';
Error report -
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
*Action: Specify only valid options.
Any ideas what is wrong here?