7 Replies Latest reply: Jan 19, 2013 12:08 PM by sb92075 RSS

    Updating fails because of a foreign key constraint

    985912
      Hello.

      I have several tables in my data base however 3 are important. One of them is Employee(Employee_ID, Name, Last_Name and other irrelevant columns), Dentist(Employee_ID, Qualifications) and Assistant(Employee_ID, Qualifications).
      I'm making an application in Oracle Application Express and upon attempting to update a row in the Employee table I get a ORA-20505: Error in DML: p_rowid=96, p_alt_rowid=Employee_ID, p_rowid2=, p_alt_rowid2=. ORA-02292: integrity constraint (DB.Dentist_CON) violated - child record found

      Now, to begin with I had the same problems with deleting rows but I solved that by adding an on cascade delete to my foreign keys.
      I have foreign key constraints both in my Dentist and Assistant tables in the Employee_ID columns and these are referencing Employee_ID in the Employee table.

      When I'm updating anything within my form in Application Express, I haven't allowed the users to do anything to the primary key (Employee_ID) from my Employee table. So any changes that might happen are to columns irrelevant to the primary key. For instance, if I change the Name column of a particular row I can't update because I get the above-mentioned error.

      I don't understand why this is happening.
      If I could have any insight into why this is happening or help of any form I'd greatly appreciate it.

      Thanks in advance.
        • 1. Re: Updating fails because of a foreign key constraint
          sb92075
          982909 wrote:
          Hello.

          I have several tables in my data base however 3 are important. One of them is Employee(Employee_ID, Name, Last_Name and other irrelevant columns), Dentist(Employee_ID, Qualifications) and Assistant(Employee_ID, Qualifications).
          I'm making an application in Oracle Application Express and upon attempting to update a row in the Employee table I get a ORA-20505: Error in DML: p_rowid=96, p_alt_rowid=Employee_ID, p_rowid2=, p_alt_rowid2=. ORA-02292: integrity constraint (DB.Dentist_CON) violated - child record found

          Now, to begin with I had the same problems with deleting rows but I solved that by adding an on cascade delete to my foreign keys.
          I have foreign key constraints both in my Dentist and Assistant tables in the Employee_ID columns and these are referencing Employee_ID in the Employee table.

          When I'm updating anything within my form in Application Express, I haven't allowed the users to do anything to the primary key (Employee_ID) from my Employee table. So any changes that might happen are to columns irrelevant to the primary key. For instance, if I change the Name column of a particular row I can't update because I get the above-mentioned error.

          I don't understand why this is happening.
          If I could have any insight into why this is happening or help of any form I'd greatly appreciate it.

          Thanks in advance.
          02292, 00000,"integrity constraint (%s.%s) violated - child record found"
          // *Cause: attempted to delete a parent key value that had a foreign
          //      key dependency.
          // *Action: delete dependencies first then parent or disable constraint.
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ

          we speak SQL.
          do you speak SQL.

          in the future SHOW us what you do using COPY & PASTE,
          so we can see exactly what you do & how Oracle responds
          • 2. Re: Updating fails because of a foreign key constraint
            985912
            Certainly.

            I created Employee using the following. There is one trigger as well which I'm using to populate the Employee_ID from the actual forms in Application Express.

            CREATE TABLE "Employee"
            (     "Employee_ID" NUMBER,
                 "Name" VARCHAR2(12) CONSTRAINT "IME_VRABOTEN_NN" NOT NULL ENABLE,
                 "Surname" VARCHAR2(10) CONSTRAINT "PREZIME_VRABOTEN_NN" NOT NULL ENABLE,
                 "Address" VARCHAR2(50) CONSTRAINT "ADRESA_VRABOTEN_NN" NOT NULL ENABLE,
                 "Telephone" VARCHAR2(10),
                 "Personal_ID_Number" VARCHAR2(13),
                 "Date_of_Employment" VARCHAR2(10) CONSTRAINT "DATA_NN" NOT NULL ENABLE,
                 "Salary" NUMBER(6,0),
                 CONSTRAINT "Employee_ID_con" PRIMARY KEY ("Employee_ID") ENABLE,
                 CONSTRAINT "Employee_CON" UNIQUE ("Personal_ID_Number", "Telephone") ENABLE
            )
            /

            CREATE OR REPLACE TRIGGER "Employee_T1"
            BEFORE
            insert or update on "Employee"
            for each row
            begin
            select employee_sequence.nextval into :new.employee_id from dual;
            end;
            /
            ALTER TRIGGER "Employee_T1" ENABLE
            /


            For the table Dentist.


            CREATE TABLE "Dentist"
            (     "Employee_ID" NUMBER,
                 "Qualifications" VARCHAR2(50),
                 CONSTRAINT "RB_VRAB_STOMATOLOG_PK" PRIMARY KEY ("Employee_ID") ENABLE
            )
            /
            ALTER TABLE "Employee_ID" ADD CONSTRAINT "Dentist_CON" FOREIGN KEY ("Employee_ID")
                 REFERENCES "Employee" ("Employee_ID") ON DELETE CASCADE ENABLE
            /

            CREATE OR REPLACE TRIGGER "Dentist_T1"
            BEFORE
            insert or update or delete on "Dentist"
            for each row
            begin
            select employee_sequence.currval into :new.employee_id from dual; //The trigger is disabled.
            end;
            /
            ALTER TRIGGER "Dentist_T1" DISABLE
            /


            And for Assistant.


            CREATE TABLE "ASSISTANT"
            (     "Employee_ID" NUMBER,
                 "Qualifications" VARCHAR2(50),
                 CONSTRAINT "Employee_ID_Assistant" PRIMARY KEY ("Employee_ID") ENABLE
            )
            /
            ALTER TABLE "ASSISTANT" ADD CONSTRAINT "ASSISTANT_CON" FOREIGN KEY ("Employee_ID")
                 REFERENCES "Employee" ("Employee_ID") ON DELETE CASCADE DISABLE
            /

            CREATE OR REPLACE TRIGGER "ASSISTANT_T1"
            BEFORE
            insert or update or delete on "ASSISTANT"
            for each row
            begin
            select employee_sequence.currval into :new.employee_id from dual; //The trigger is again disabled.
            end;
            /
            ALTER TRIGGER "ASSISTANT_T1" ENABLE
            /


            I can't show you what I'm trying to do using copy and paste because I'm doing this in Application Express like I explained above. Here are the tables, triggers and foreign key constraints. The problem is, as I said, when I try to update a value (using an report and form, simply by clicking on the edit button, changing the value in the Name text field and clicking Apply Changes) I get the error I pasted earlier.

            I hope this helps.
            • 3. Re: Updating fails because of a foreign key constraint
              sb92075
              What the error is trying to explain is that you are attempting to change a column value;
              but by doing so would orphan some child row that has a FK relationship to that parent value.
              • 4. Re: Updating fails because of a foreign key constraint
                985912
                But it's not true. If I'm trying to change Name from Employee and update that row in Employee WITHOUT changing the value of Employee_ID how can I possibly change the child row? This is what's confusing me and I don't have any idea about how to solve it.
                • 5. Re: Updating fails because of a foreign key constraint
                  sb92075
                  982909 wrote:
                  But it's not true. If I'm trying to change Name from Employee and update that row in Employee WITHOUT changing the value of Employee_ID how can I possibly change the child row? This is what's confusing me and I don't have any idea about how to solve it.
                  the code below shows above is NOT true

                  CREATE OR REPLACE TRIGGER "Employee_T1"
                  BEFORE
                  insert or update on "Employee"
                  for each row
                  begin
                  select employee_sequence.nextval into :new.employee_id from dual;
                  end;
                  /

                  TRIGGER above changes EMPLOYEE_ID for any UPDATE statement!
                  • 6. Re: Updating fails because of a foreign key constraint
                    985912
                    Thank you. It works now. :D
                    • 7. Re: Updating fails because of a foreign key constraint
                      sb92075
                      982909 wrote:
                      Thank you. It works now. :D
                      you need to learn to BELIEVE what the error message tells you.
                      Oracle is too dumb to lie to you about what is wrong!