This discussion is archived
7 Replies Latest reply: Jan 19, 2013 10:08 AM by sb92075 RSS

Updating fails because of a foreign key constraint

985912 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you. It works now. :D
  • 7. Re: Updating fails because of a foreign key constraint
    sb92075 Guru
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points