6 Replies Latest reply on Oct 9, 2003 10:48 AM by KevinDClarke

    Foreign Key Reference a View problem..

    Simon Greener
      Anyone help me sort out the following...

      I have a (LOOKUP) table which provides the domain of
      all possible values of the CAUSES of tree decline...

      CREATE TABLE CAUSE (
      CAUSE char(3),
      COMMENTS varchar2(80),
      LABEL varchar2(8));

      ALTER TABLE CAUSE ADD ( CONSTRAINT CAUSE_PK PRIMARY KEY (CAUSE) );

      However, only a set list of actual CAUSE values are valid for the CAUSE item in one of the two following tables...

      CREATE TABLE HLTH_EUC_A (
      FEATUREID number(38) NOT NULL,
      ....
      CAUSE char(3) NOT NULL .... )

      and

      CREATE TABLE HLTH_PINE_A (
      FEATUREID number(38) NOT NULL,
      ....
      CAUSE char(3) NOT NULL .... )

      (The FEATUREID column is the primary key in both tables.)

      Now I want to create a FOREIGN KEY REFERENCE to ensure
      that the value entered into these tables is correct for
      the tree type the table represents (Pine or Euc)...

      I tried to create two views to represent the correct
      domain for each tree type...

      CREATE OR REPLACE VIEW CAUSEEUCS AS
      SELECT CAUSE,COMMENTS,LABEL
      FROM CAUSE
      WHERE CAUSE IN ('AGM','ARM','BLU','BO1','BO2','CHR','COR','CRY','DRA','DRO','END','EXP','FIR','FRO','HAI','HER','JRT','LIG','MA1','MA2','MUL','MYC','OTH','PHY','PLA','PO1','PO2','PO3','REP','ROO','SIT','SNO','SOI','STB','STE','TRF','UNK','WEE','WI1','WI2');

      CREATE OR REPLACE VIEW CAUSEPINES AS
      SELECT CAUSE,COMMENTS,LABEL
      FROM CAUSE
      WHERE CAUSE IN ('ARM','BOR','COP','CYC','DOT','DRA','DRO','ESI','EXP','FIR','FRO','HAI','HER','HYL','JRT','LIG','MAG','MA1','MA2','MUL','NIT','OTH','PAI','PHO','PHY','PIA','PIL','PIS','PLA','PO1','PO2','PO3','POT','REP','ROO','SIR','SIT','SNC','SNO','SOI');

      But when I tried the following...

      ALTER TABLE HLTH_EUC_A ADD (
      CONSTRAINT HLTH_EUC_A_CAUSEEUCS_FK8 FOREIGN KEY (CAUSE) REFERENCES CAUSEEUCS (CAUSE) );

      ALTER TABLE HLTH_PINE_A ADD (
      CONSTRAINT HLTH_PINE_A_CAUSEEUCS_FK8 FOREIGN KEY (CAUSE) REFERENCES CAUSEPINES (CAUSE) );

      I get the following error:

      ORA-02444: Cannot resolve referenced object in referential constraints

      Has anyone any suggestions as to what I could do?

      regards
      Simon
        • 1. Re: Foreign Key Reference a View problem..
          JustinCave
          Is there a reason that you don't want to use a CHECK constraint here to enforce the subset requirement? That seems like a much more straightforward approach.

          Justin
          Distributed Database Consulting, Inc.
          www.ddbcinc.com
          • 2. Re: Foreign Key Reference a View problem..
            APC
            Justin

            Using a CHECK constraint would require hard-coding duplicate values into multiple tables, which is icky and hard to maintain.

            Simon

            If it's possible to re-design your tables I think you should. Something like this:

            TREE_HEALTH
            FEATUREID number(38) NOT NULL,
            TREE char(3) NOT NULL,
            CAUSE char(3) NOT NULL .... )

            TREE_TYPE
            TREE char(3) NOT NULL,
            COMMENTS varchar2(80),
            LABEL varchar2(8));

            CAUSE_TYPE
            CAUSE char(3) NOT NULL,
            COMMENTS varchar2(80),
            LABEL varchar2(8));

            TREE_CAUSE
            TREE char(3) NOT NULL
            CAUSE char(3) NOT NULL ;

            In other words, have one table for all your ill trees, and use tree type to distinguish eucalyptus from ash. TREE_CAUSE is an intersection table to record valid combinations of trees and diseases, and this is what forms your foreign key on TREE_HEALTH. TREE_CAUSE has foreign keys on TREE_TYPE and CAUSE_TYPE.

            Cheers, APC
            • 3. Re: Foreign Key Reference a View problem..
              JustinCave
              Andrew-

              Excellent call. If redefining the tables is an option, your suggestions are much better than what he has now.

              Justin
              Distributed Database Consulting, Inc.
              www.ddbcinc.com
              • 4. Re: Foreign Key Reference a View problem..
                Simon Greener
                Thanks fellows for the suggestions.

                I can see what you are suggesting makes sense
                except that the tables for Eucs and Pines have
                slightly different structures. Also, there are
                many different Eucs species and since there is no need
                to differentiate diseases among the Euc species,
                I can see that the number of records to code in the
                TREE_CAUSE table could become quite large. Also I would
                have to start using views to present the data the way
                the client wants it if I moved to a single table for
                the tree data.

                Hmmm I'll think about it. Thanks for taking the time
                to respond!

                regards
                Simon
                • 5. Re: Foreign Key Reference a View problem..
                  APC
                  except that the tables for Eucs and Pines have
                  slightly different structures

                  This is the classic dilemma when handling sub-types: multiple tables or redundant columns. There are pros and cons on both sides, but if the differences are "slight" then I would tend to come down on the side of the single table. It is important to ensure common attributes have common columns.
                  there are many different Eucs species and since there is no need
                  to differentiate diseases among the Euc species, I can see that the number
                  of records to code in the TREE_CAUSE table could become quite large.
                  I'm not a arbologist, so I may have the wrong terminology, but maybe you need a two-part tree type - genus ("Eucalyptus") and species ("silver tipped"). Then you can record diseases against the genus or the genus+species, depending on how specific it is.

                  I suppose maintenance is less of an issue for you, as I expect it's pretty rare to find a new tree disease, so Justin's solution may work for you. I guess it depends on your forest's diversity.

                  Cheers, APC
                  • 6. Re: Foreign Key Reference a View problem..
                    KevinDClarke
                    I'd just like to agree that the inability to handle sub-types in foreign keys has always seemed an important omission which deserves an enhancement.

                    A simple example:
                    Table PERSONS with columns ID, NAME, GENDER, MOTHER_ID, FATHER_ID.

                    It ought to be possible to define a referential constraint
                    so that the father must be a male person and mother female. Allowing views instead of tables OR allowing constants instead of columns could be alternative ways to do this.