11 Replies Latest reply: Jan 11, 2013 7:14 AM by Stew Ashton RSS

    Is it possible to have foreign key, reference to another  table indirectly?

    982630
      I have 4 tables, to hold data for issues related to operating machines, only some of the machines are planned to run on that respective operating years.
      "YEARS" contains operating years eg. 2009, 2010, 2011, 2012 etc,
      "MONTHS" table contains month no, operating year to get month id to track issues on monthly basis.
      "MACHINES" table contains list of machine along with planned operating year. i.e. if we have machine1,machine2,machine3 then only machine1 and machine2 are planed to run on 2012. Primary key consists of 2 columns machine id and oper_year.
      "MACHINE_PROBLEMS" table contains issues related to machines on that operating year.
      With this table I need to make a foreign key with MACHINES table, so that I can only choose machines that matching with OPER_YEAR of MACHINES table and OPER_YEAR of MONTHS table
      (related by MONTH_ID of MACHINE_PROBLEMS and MONTHS)

      In other words user should not try to insert machine planned to run on another operating year. In MACHINE_PROBLEMS table operating year is decided by MONTH_ID column.

      This could be possibly solved by a trigger, but just for learning I need to know this type of indirect Foreign key reference is possible or not.
      Tables and problem is mentioned below.

      Sample Data:
      "Years" Table : ("OPER_YEAR",...) (2010,...) (2011,...) (2012,...) (2013,...)

      "MONTHS" Table : ("MonthID","MonthNo","Oper_year") (201201, 1, 2012) (201202, 2, 2012) (201203, 3, 2012) (201204, 4, 2012) (201301, 1, 2013)

      "MACHINES" Table : ("MACHINE_SERIAL_NO", "OPER_YEAR",..)(Machine1, 2012,..) (Machine2, 2012,..) (Machine3, 2013,..)

      "MACHINE_PROBLEMS" Table: ("MACHINE_SERIAL_NO","MONTH_ID","PROBLEM"...) ("Machine1",201204,"blah blah") valid data

      ("MACHINE_SERIAL_NO","MONTH_ID","PROBLEM"...) ("Machine3",201204,"blah blah") invalid data since machine3 is not planned to operate in 2012.

      CREATE TABLE  "YEARS"
         (     "OPER_YEAR" NUMBER NOT NULL ENABLE,
              "OTHER_COLUMNS" NUMBER NOT NULL ENABLE,
             CONSTRAINT "YEARS_PK" PRIMARY KEY ("OPER_YEAR") ENABLE   
          )
      CREATE TABLE  "MONTHS"
         (     "MONTH_ID" NUMBER NOT NULL ENABLE,
              "MONTH_NO" NUMBER NOT NULL ENABLE,
           "OPER_YEAR" NUMBER NOT NULL ENABLE,          
             CONSTRAINT "MONTHS_PK" PRIMARY KEY ("MONTH_ID") ENABLE   
          )
      ALTER TABLE  "MONTHS" ADD CONSTRAINT "MONTHS_FK1" FOREIGN KEY ("OPER_YEAR") REFERENCES  "YEARS" ("OPER_YEAR") ENABLE
      
           
      CREATE TABLE  "MACHINES" 
         (     "MACHINE_SERIAL_NO" VARCHAR2(100) NOT NULL ENABLE,
              "OPER_YEAR" NUMBER NOT NULL ENABLE,
           CONSTRAINT "MACHINES_PK1" PRIMARY KEY ("MACHINE_SERIAL_NO", "OPER_YEAR") ENABLE,
          )
      
      CREATE TABLE "MACHINE_PROBLEMS"
      (     "PROBLEM_ID" NUMBER NOT NULL ENABLE,
              "MACHINE_SERIAL_NO" VARCHAR2(100) NOT NULL ENABLE,
           "PROBLEM" VARCHAR2(100) NOT NULL ENABLE,
           "DATE1" DATE NOT NULL ENABLE,
           "MONTH_ID" NUMBER NOT NULL ENABLE,
           CONSTRAINT "MACHINE_PROBLEMS_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE
      )     
      ALTER TABLE  "MACHINE_PROBLEMS" ADD CONSTRAINT "MACHINE_PROBLEMS_FK1" FOREIGN KEY ("MONTH_ID")       REFERENCES  "MONTHS" ("MONTH_ID") ENABLE
      
      
      ALTER TABLE  "MACHINE_PROBLEMS" ADD CONSTRAINT "MACHINE_PROBLEMS_FK1" 
                           FOREIGN KEY ("MACHINE_SERIAL_NO", ***HERE I NEED TO REFER "OPER_YEAR" through MONTHS table using MONTH_ID relation"   )       REFERENCES  "MACHINES" ("MACHINE_SERIAL_NO","OPER_YEAR") ENABLE
      Edited by: user8184292 on Jan 10, 2013 10:35 AM
      data type in "MACHINE_SERIAL_NO" of MACHINE_PROBLEMS corrected
        • 1. Re: Is it possible to have foreign key, reference to another  table indirectly?
          Keith Jamieson
          Some of your scripts are incorrect. eg You are referencing year_id but your column is called oper_year.
          eg

          ALTER TABLE "MONTHS" ADD CONSTRAINT "MONTHS_FK1" FOREIGN KEY ("YEAR_ID") REFERENCES "YEARS" ("YEAR_ID") ENABLE

          but column year_id is not in the table Months

          In addition when you create the table machines you specify the primary key plant_id,oper_year but you dont have the column plant_id on the table.


          Thanks for at least trying to provide the create sample statements but you should really test them before posting them. Its better than no statements at all

          Can you supply insert data statements for your sample data rather than just posting them as you did. Its a lot easier for us to give you help then.
          • 2. Re: Is it possible to have foreign key, reference to another  table indirectly?
            982630
            thanks I just realized, it was corrected.
            • 3. Re: Is it possible to have foreign key, reference to another  table indirectly?
              Stew Ashton
              You can create a "refresh on commit" materialized view with the columns you want as its primary key, then add a foreign key constraint to that. The constraint must be deferrable; since the MV is refreshed at commit time, the constraint must be evaluated at commit time as well.

              "Refresh on commit" MVs involve some serialization, but I imagine that's not a problem for control tables like these.
              CREATE MATERIALIZED VIEW LOG ON MACHINES
              WITH SEQUENCE, ROWID, PRIMARY KEY, COMMIT SCN INCLUDING NEW VALUES;
              
              CREATE MATERIALIZED VIEW LOG ON MONTHS
              WITH SEQUENCE, ROWID, PRIMARY KEY, COMMIT SCN (OPER_YEAR) INCLUDING NEW VALUES;
              
              create materialized view machines_months refresh fast on commit
              AS
              SELECT A.ROWID ARID, B.ROWID BRID, A.MACHINE_SERIAL_NO, B.MONTH_ID
              FROM MACHINES A, MONTHS B
              WHERE A.OPER_YEAR = B.OPER_YEAR;
              
              ALTER TABLE MACHINES_MONTHS ADD CONSTRAINT PK_MM PRIMARY KEY(MACHINE_SERIAL_NO, MONTH_ID);
              
              ALTER TABLE  "MACHINE_PROBLEMS" ADD CONSTRAINT "MACHINE_PROBLEMS_FK2" 
              FOREIGN KEY ("MACHINE_SERIAL_NO", month_id  )       REFERENCES  machines_months deferrable ENABLE;
              • 4. Re: Is it possible to have foreign key, reference to another  table indirectly?
                982630
                These are the sample inserts.
                INSERT INTO "YEARS" (OPER_YEAR, OTHER_COLUMNS) VALUES ('2010','45');  -- "OTHER_COLUMNS" does not make any sense in this issue.
                INSERT INTO "YEARS" (OPER_YEAR, OTHER_COLUMNS) VALUES ('2011','46');
                INSERT INTO "YEARS" (OPER_YEAR, OTHER_COLUMNS) VALUES ('2012','85');
                INSERT INTO "YEARS" (OPER_YEAR, OTHER_COLUMNS) VALUES ('2013','76');
                
                INSERT INTO "MONTHS" (MONTH_ID, MONTH_NO, OPER_YEAR) VALUES ('201208', '8', '2012');  --AUGUST 2012
                INSERT INTO "MONTHS" (MONTH_ID, MONTH_NO, OPER_YEAR) VALUES ('201209','9', '2012');  -- SEPTEMBER 2012
                INSERT INTO "MONTHS" (MONTH_ID, MONTH_NO, OPER_YEAR) VALUES ('201209', '10', '2012');  -- OCTOBER 2012
                INSERT INTO "MONTHS" (MONTH_ID, MONTH_NO, OPER_YEAR) VALUES ('201301', '1', '2013');   -- JANUARY 2013
                
                INSERT INTO "MACHINES" (MACHINE_SERIAL_NO, OPER_YEAR) VALUES ('Machine1', '2011');
                INSERT INTO "MACHINES" (MACHINE_SERIAL_NO, OPER_YEAR) VALUES ('Machine2', '2011');
                INSERT INTO "MACHINES" (MACHINE_SERIAL_NO, OPER_YEAR) VALUES ('Machine1', '2012');
                INSERT INTO "MACHINES" (MACHINE_SERIAL_NO, OPER_YEAR) VALUES ('Machine2', '2012');
                INSERT INTO "MACHINES" (MACHINE_SERIAL_NO, OPER_YEAR) VALUES ('Machine3', '2013');
                
                INSERT INTO "MACHINE_PROBLEMS" (PROBLEM_ID, MACHINE_SERIAL_NO, PROBLEM, DATE1, MONTH_ID) VALUES ('1','Machine1','Machine failed on bearing wear', '12/AUG/2012', '201208'); --DATA VALID 
                
                INSERT INTO "MACHINE_PROBLEMS" (PROBLEM_ID, MACHINE_SERIAL_NO, PROBLEM, DATE1, MONTH_ID) VALUES ('2','Machine3','Machine failed reason 2', '08/JAN/2013', '201301');          --DATA VALID
                
                INSERT INTO "MACHINE_PROBLEMS" (PROBLEM_ID, MACHINE_SERIAL_NO, PROBLEM, DATE1, MONTH_ID) VALUES ('3','Machine3','Machine failed reason 3', '14/10/2012', '201210');            *--DATA NOT VALID*
                Last insert should not be allowed since 'Machine3' is not a planned to run in 2013.
                • 5. Re: Is it possible to have foreign key, reference to another  table indirectly?
                  rp0428
                  >
                  "MACHINE_PROBLEMS" table contains issues related to machines on that operating year.
                  With this table I need to make a foreign key with MACHINES table, so that I can only choose machines that matching with OPER_YEAR of MACHINES table and OPER_YEAR of MONTHS table
                  (related by MONTH_ID of MACHINE_PROBLEMS and MONTHS)
                  >
                  Your architecture appears to be flawed. Your problem statement implies that you want to track data by DATE (month/year) by you are using date components instead of just using DATE datatypes.

                  Why are you trying to do it this way? You should standardize the data model.

                  MACHINES - this is NOT a table of machines. It is a table of machines per operating year.
                  You need a table that represents UNIQUE machines. A machine is unique in and of itself; it doesn't become unique because of the year it operates in.

                  You need to normalize this table and restrict the data in it to data that defines a UNIQUE machine. OPER_YEAR should be removed from this table. If you want to track the specifics years that the machine is in operation you could add a SERVICE_START_DATE column to indicate the first day the machine went into service.
                  CREATE TABLE MACHINES
                    (MACHINE_SERIAL_NO VARCHAR2(100),
                     FIRST_SERVICE_START_DATE, -- first time the machine ever went into service
                     SERVICE_START_DATE, -- start date of the current active service period
                     FINAL_SERVICE_END_DATE, -- final end date if desired
                     STATUS -- NULL, ACTICE, BEING_SERVICED, OUT_OF_SERVICE - tracks the current status of this machine
                    )
                  And then create a MACHINE_SERVICE_DATE table with a minimal structure like
                  CREATE TABLE MACHINE_SERVICE_DATE
                    (MACHINE_SERIAL_NO VARCHAR2(100),
                     SERVICE_START_DATE DATE,
                     SERVICE_END_DATE DATE,
                     . . .
                    )
                  This table could be the history table for the periods that the machine was in service. When a machine goes out of service you update the status in the new MACHINES table and add a record to this table that has the SERVICE_START_DATE from the new machines table and the OUT_OF_SERVICE date.

                  Using your MACHINES table it looks like you want one record for any year in which the machine operated. That means that evey year you need to add new records for ALL machines if they are still operating.

                  This is exactly OPPOSITE the standard method of maintaining status. The standard way is to add information only for machines that GO OUT OF SERVICE.

                  If you had 1 million machines and 10 go out of service every year your method adds close to one million new records for the machines still in service rather than update 10 records to indicate those machines are out of service.

                  That is backwards. And when you do things backwards you should really examine why.

                  MACHINE_PROBLEMS - For this table it appears you are trying to track both the date the problem happened and also a MONTH_ID to represent something and you want to make sure MONTH_ID represents a value in the MONTHS table.

                  Again, why? If a problem happens to a machine (unique by MACHINE_SERIAL_NO) it happens on a date. It doesn't matter if that date is a date in your OPER_YEAR table or not. The problem happened, it happened on a date and you need to capture that date.

                  The value of OPER_YEAR, in any table, will refer to one, and only one, year. Your issue is that the MONTHS table might have more than one MONTH_ID for the same OPER_YEAR. In fact, with that DDL you could create millions of records with the same OPER_YEAR value and the same MONTH_NO.

                  You don't really need the MONTHS table since all of that information is available from a DATE value.

                  Review your data model and modify it to use third normal form. When you think you have the data model you want conduct a walkthrough with your team using the different data scenarios (add new machine, take a machine out of service, put a machine back into service, log a problem for a machine, etc) to make sure you haven't missed something important.
                  • 6. Re: Is it possible to have foreign key, reference to another  table indirectly?
                    982630
                    Ashton,
                    Thanks for your suggestion, I'm not aware of materialized views, but it looks like too complicated for this problem at least to me,
                    Anyway I was trying to test this to learn more about materialized view.
                    While attempting to create material view this error is coming.

                    Error starting at line 1 in command:
                    CREATE MATERIALIZED VIEW LOG ON MACHINES
                    WITH SEQUENCE, ROWID, PRIMARY KEY, COMMIT SCN INCLUDING NEW VALUES
                    Error at Command Line:1 Column:0
                    Error report:
                    SQL Error: ORA-00439: feature not enabled: Advanced replication
                    00439. 00000 - "feature not enabled: %s"
                    *Cause:    The specified feature is not enabled.
                    *Action:   Do not attempt to use this feature.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                    • 7. Re: Is it possible to have foreign key, reference to another  table indirectly?
                      982630
                      to rp0428
                      First thanks for looking into the problem.
                      my apologies, for not mentioning that the above tables are only a partial sample representation of my system, only just to represent the foreign key problem.
                      rp0428 wrote:
                      >
                      "MACHINE_PROBLEMS" table contains issues related to machines on that operating year.
                      With this table I need to make a foreign key with MACHINES table, so that I can only choose machines that matching with OPER_YEAR of MACHINES table and OPER_YEAR of MONTHS table
                      (related by MONTH_ID of MACHINE_PROBLEMS and MONTHS)
                      >
                      Your architecture appears to be flawed. Your problem statement implies that you want to track data by DATE (month/year) by you are using date components instead of just using DATE datatypes.

                      Why are you trying to do it this way? You should standardize the data model.
                      This is for monthly monitoring of a planned operating year. In addition to those listed 4 tables, there are many other tables in which monthly information are inputted for monitoring.
                      So it was necessary to use only month and year, the day component is irrelevant in this situation. "DATE1" Date in MACHINE_PROBLEMS is not relevant in major domain.



                      >
                      MACHINES - this is NOT a table of machines. It is a table of machines per operating year.
                      You need a table that represents UNIQUE machines. A machine is unique in and of itself; it doesn't become unique because of the year it operates in.
                      You need to normalize this table and restrict the data in it to data that defines a UNIQUE machine. OPER_YEAR should be removed from this table. If you want to track the specifics years that the machine is in operation you could add a SERVICE_START_DATE column to indicate the first day the machine went into service.
                      CREATE TABLE MACHINES
                      (MACHINE_SERIAL_NO VARCHAR2(100),
                      FIRST_SERVICE_START_DATE, -- first time the machine ever went into service
                      SERVICE_START_DATE, -- start date of the current active service period
                      FINAL_SERVICE_END_DATE, -- final end date if desired
                      STATUS -- NULL, ACTICE, BEING_SERVICED, OUT_OF_SERVICE - tracks the current status of this machine
                      )
                      And then create a MACHINE_SERVICE_DATE table with a minimal structure like
                      CREATE TABLE MACHINE_SERVICE_DATE
                      (MACHINE_SERIAL_NO VARCHAR2(100),
                      SERVICE_START_DATE DATE,
                      SERVICE_END_DATE DATE,
                      . . .
                      )
                      This table could be the history table for the periods that the machine was in service. When a machine goes out of service you update the status in the new MACHINES table and add a record to this table that has the SERVICE_START_DATE from the new machines table and the OUT_OF_SERVICE date.
                      
                      Using your MACHINES table it looks like you want one record for any year in which the machine operated. That means that evey year you need to add new records for ALL machines if they are still operating.
                      
                      This is exactly OPPOSITE the standard method of maintaining status. The standard way is to add information only for machines that GO OUT OF SERVICE.
                      
                      If you had 1 million machines and 10 go out of service every year your method adds close to one million new records for the machines still in service rather than update 10 records to indicate those machines are out of service.
                      
                      That is backwards. And when you do things backwards you should really examine why.
                      You are right, This is "active machines per operating year". In original table "MACHINE_SERIAL_NO" and "OPER_YEAR" together constitutes a "unique machine on an operating year".

                      I was first thought of splitting this into unique machine details table, and "operating year machine table" referring to "unique machine" table,
                      This will need a inner join of two tables, which is not much a problem at first look,
                      but I had a lot of procedures and function which already have many Inner Left and right joins, and rewriting them by adding one more inner join was an overkill to my head,
                      So I was thought of not disturb them and go with presented method.

                      I know this method is not perfect, but considering that only less than 50 machines will be there per year, for 10 years only 500 rows or less will be inserted, this looks not bad to me.



                      MACHINE_PROBLEMS - For this table it appears you are trying to track both the date the problem happened and also a MONTH_ID to represent something and you want to make sure MONTH_ID represents a value in the MONTHS table.

                      Again, why? If a problem happens to a machine (unique by MACHINE_SERIAL_NO) it happens on a date. It doesn't matter if that date is a date in your OPER_YEAR table or not. The problem happened, it happened on a date and you need to capture that date.
                      Yes there is something here I need to consider. I added a month_id in order to make my query and procedures comparatively easy to me with some special procedures that contained many complicated formula with pl/sql.


                      The value of OPER_YEAR, in any table, will refer to one, and only one, year. Your issue is that the MONTHS table might have more than one MONTH_ID for the same OPER_YEAR. In fact, with that DDL you could create millions of records with the same OPER_YEAR value and the same MONTH_NO.

                      You don't really need the MONTHS table since all of that information is available from a DATE value.
                      MONTHS table was necessary as I explained first.

                      >
                      Review your data model and modify it to use third normal form. When you think you have the data model you want conduct a walkthrough with your team using the different data scenarios (add new machine, take a machine out of service, put a machine back into service, log a problem for a machine, etc) to make sure you haven't missed something important.
                      I really appreciates your suggestions and takes into account.
                      • 8. Re: Is it possible to have foreign key, reference to another  table indirectly?
                        rp0428
                        Then you need to provide some sample data for each of those tables and then a step-by-step explanation of how the data should be loaded into the tables and what validity checks need to be performed on the data.
                        • 9. Re: Is it possible to have foreign key, reference to another  table indirectly?
                          982630
                          Sample data was given as insert statements in one of the above posts.
                          • 10. Re: Is it possible to have foreign key, reference to another  table indirectly?
                            rp0428
                            Seems you missed this part
                            >
                            and then a step-by-step explanation of how the data should be loaded into the tables and what validity checks need to be performed on the data.
                            >
                            Give us a walk through of what you expect to have happen to the data as you put it into the tables. What columns should be checked, what should happen if the checks fail and so on.

                            Show us the use cases the data needs to support. That is what drives the architecture and design.
                            • 11. Re: Is it possible to have foreign key, reference to another  table indirectly?
                              Stew Ashton
                              You must be on an earlier version of Oracle.

                              Remove the expression "COMMIT SCN" from the commands that create the MV log.

                              Funny you say it's too complicated. Do you think the triggers would be simpler?

                              By the way, I find rp's comments on proper data modeling very valuable.

                              Edited by: Stew Ashton on Jan 11, 2013 2:14 PM