3 Replies Latest reply on May 4, 2016 4:57 AM by petehug

    Problems with typed relations

    petehug

      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


      Problem 1:

       

      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"

      *Cause:   

      *Action:

       

      (so I omitted the FOREIGN KEY constraints and did the same with other statements but I cannot see what is wrong with the above)

       

       

      Problem 2:

       

      I want to enforce the typed relations and tried to add this materialized view:

       

      create materialized view "PERS_MV"

         build immediate

         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

                 commit time.

      *Action:   Specify only valid options.

       

      Any ideas what is wrong here?

       

      Thanks!

        • 1. Re: Problems with typed relations
          gaverill

          Under the section "Indexes and Constraints", this article demonstrates how to use virtual columns to enforce "conditional" foreign key relationships...

           

          http://www.oracle-developer.net/display.php?id=510

           

          Gerard

          1 person found this helpful
          • 2. Re: Problems with typed relations
            Barbara Boehmer

            There seems to be a bug in some versions, including mine, such that you cannot use USING INDEX with a constraint.  You can still create the constraint, but without an index.  I know it is bad to have unindexed foreign keys, but I can't find a workaround.  I tried various things, such as creating the table, then creating the indexes, then altering the table and trying to add the constraints using the indexes, but nothing worked.  Everything either produced some misleading error about missing a parenthesis or an invalid alter table command with the asterisk pointing to the USING INDEX.  Some internet research also reveals that others have experienced the same errors in one version while the identical syntax works in another version.  The following demonstrates enforcement of your rules, using two virtual columns and constraints, but without indexes.  You do not need a materialized view to enforce your rules.

             

            SCOTT@orcl> SELECT BANNER FROM v$version
              2  /
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
            PL/SQL Release 12.1.0.2.0 - Production
            CORE    12.1.0.2.0    Production
            TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
            NLSRTL Version 12.1.0.2.0 - Production
            
            5 rows selected.
            
            SCOTT@orcl> CREATE TABLE person
              2  ( id          NUMBER(9,0)    NOT NULL
              3  , name          VARCHAR2(30)    NOT NULL
              4  , gender          VARCHAR2(1)    NOT NULL
              5  , fatherid       NUMBER(9,0)    NULL
              6  , motherid       NUMBER(9,0)    NULL
              7  , father_gender  VARCHAR2(1)    GENERATED ALWAYS AS ('M') VIRTUAL
              8  , mother_gender  VARCHAR2(1)    GENERATED ALWAYS AS ('F') VIRTUAL
              9  , CONSTRAINT     ck_person_gender    CHECK (gender IN ('M', 'F'))
             10  , CONSTRAINT     pk_person     PRIMARY KEY (id)
             11  , CONSTRAINT     uk_person     UNIQUE (id, gender)
             12  , CONSTRAINT     fk_person_father    FOREIGN KEY (fatherid, father_gender)
             13                      REFERENCES person (id, gender) ON DELETE CASCADE
             14  , CONSTRAINT     fk_person_mother    FOREIGN KEY (motherid, mother_gender)
             15                      REFERENCES person (id, gender) ON DELETE CASCADE
             16  )
             17  TABLESPACE users
             18  /
            
            Table created.
            
            SCOTT@orcl> -- valid inserts:
            SCOTT@orcl> INSERT INTO person (id, name, gender) VALUES (1, 'Noel', 'M')
              2  /
            
            1 row created.
            
            SCOTT@orcl> INSERT INTO person (id, name, gender) VALUES (2, 'Ann', 'F')
              2  /
            
            1 row created.
            
            SCOTT@orcl> INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (3, 'Barbara', 'F', 1, 2)
              2  /
            
            1 row created.
            
            SCOTT@orcl> -- invalid inserts:
            SCOTT@orcl> INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (4, 'Lisa', 'X', 1, 2)
              2  /
            INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (4, 'Lisa', 'X', 1, 2)
            *
            ERROR at line 1:
            ORA-02290: check constraint (SCOTT.CK_PERSON_GENDER) violated
            
            
            SCOTT@orcl> INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (3, 'Lisa', 'F', 1, 2)
              2  /
            INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (3, 'Lisa', 'F', 1, 2)
            *
            ERROR at line 1:
            ORA-00001: unique constraint (SCOTT.PK_PERSON) violated
            
            
            SCOTT@orcl> INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (4, 'Lisa', 'F', 2, 2)
              2  /
            INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (4, 'Lisa', 'F', 2, 2)
            *
            ERROR at line 1:
            ORA-02291: integrity constraint (SCOTT.FK_PERSON_FATHER) violated - parent key
            not found
            
            
            SCOTT@orcl> INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (4, 'Lisa', 'F', 1, 1)
              2  /
            INSERT INTO person (id, name, gender, fatherid, motherid) VALUES (4, 'Lisa', 'F', 1, 1)
            *
            ERROR at line 1:
            ORA-02291: integrity constraint (SCOTT.FK_PERSON_MOTHER) violated - parent key
            not found
            
            
            SCOTT@orcl> 
            
            
            • 3. Re: Problems with typed relations
              petehug

              Thanks Barbara, this is a great! Also thanks to Gerard who also pointed to using virtual columns.

               

              The only thing I would add is that in my version of Oracle, I can successfully use the USING INDEX clause with the PRIMARY KEY constraint, but it doesn't work at all with FOREIGN KEY constraints.