Replacing Primary Key PL SQL

Question

I am trying to write a PL/SQL procedure that will look for an existing primary key "supplier_id" from the supplier table and replacing it with a new one. The primary key "supplier_id" is also a foreign key for a few other tables. Therefore I need update the foreign key locations as well. Here is the procedure I have written to solve this:

create or replace PROCEDURE ex5b_supplier_update(supplier_id_delete IN VARCHAR2, 
  supplier_id_update IN VARCHAR2) IS
  CURSOR supplier_cursor IS
  SELECT supplier_id
  FROM supplier;
  supplier_row supplier_cursor%rowtype;
  BEGIN
  OPEN supplier_cursor;
  LOOP
  FETCH supplier_cursor INTO supplier_row;
  EXIT WHEN supplier_cursor%notfound;

IF ex5b_supplier_exist(supplier_id_delete) THEN


    UPDATE supplier
    SET supplier_id = supplier_id_update
    WHERE supplier_id = supplier_id_delete;

    UPDATE PURCHASE_ORDER
    SET supplier_id = supplier_id_update
    WHERE supplier_id = supplier_id_delete;

    UPDATE PRODUCT
    SET supplier_id = supplier_id_update
    WHERE supplier_id = supplier_id_delete;

    DBMS_OUTPUT.PUT_LINE('UPDATED');
    ELSE 
    DBMS_OUTPUT.PUT_LINE('NOT UPDATED');
END IF;
END LOOP;
CLOSE supplier_cursor;
END;

The procedure gives me the following error:

Error starting at line : 2 in command - BEGIN ex5b_supplier_update('S500','S600');

END;

Error report - ORA-02292: integrity constraint (SYSTEM.PRODUCT_FK) violated - child record found ORA-06512: at "SYSTEM.EX5B_SUPPLIER_UPDATE", line 15 ORA-06512: at line 2 02292. 00000 - "integrity constraint (%s.%s) violated - child record found" *Cause: attempted to delete a parent key value that had a foreign dependency. *Action: delete dependencies first then parent or disable constraint.

Which makes total sense you cannot delete a primary key that is used as a foreign key. But I also can't change foreign keys that have no primary keys.

So my question is how can I change the supplier_id and all its foreign keys at the same time to avoid this error?

1. Insert new parent. 2. Update children to new parent. 3. Delete old parent. Hope no one has added a new child between step 2 and 3. Or deferrable constraints. — Shannon Severance

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2015-10-05T00:23:53

In a relational database a primary key is guaranteed to be three things:

1) Not nullable
2) Unique
3) UNCHANGING

It's the third rule which you're violating here, and from the errors you're getting perhaps you see why. This way lies madness. Do not change the value of a primary key. Change the attribute values all you like, so that the row now appears to be something completely different - but do not change the primary key. If you need think you need to change the primary key what you're really saying is that your primary key is not, in fact, primary. It might be a unique key, but it is by definition not a primary key.

Primary keys do not get changed.

Best of luck.

EDIT

If you really want to "change" the primary key without disabling constraints and etc, here's what you do:

Start a transaction.
Create a new row in your table with a new ID.
Copy all attributes EXCEPT FOR THE PRIMARY KEY ID COLUMN from the "original" row to the "new" row.
Update all rows in tables with foreign key constraints which reference the "original" row to reference the "new" row, i.e. change the "old" ID value to the "new" ID value.
Delete the "original" row.
COMMIT the transaction.

When done in this manner you don't violate any of the rules regarding primary keys, and at the end of the transaction the primary key appears to have been changed and all FK's are updated.

Best of luck.

Replacing Primary Key PL SQL

1 Answers

EDIT