SQL - Best way too...

[acole1]

I have a PK I want to change, but when I try and UPDATE the PK to the new value, I get an error "ORA-02292: integrity constraint (AMC****.SYS_C00215392) violated - child record
found."

When I try and ALTER the table to drop the PK so I can change it, I get the error "ORA-02273: this unique/primary key is referenced by some foreign keys."


What is the best way to update/change this PK since it is a FK in other tables?

It is the FK in only one other table, so I could ALTER that table to not depend on this PK field, then updade this PK, then put the FK back.... I was hoping there was a cleaner way to do it though.

Thanks!!

-Aaron

 

[lozina]

did you try disabling the foreign key constraint, performing your change, then enabling the constraint back?

i think the syntax is something like:
alter table Commission disable constraint SYS_C00215392;

then later
alter table Commission enable constraint SYS_C00215392;

well actually, if you are changing the PK the FK would become invalid so you wouldnt be able to re-enable it. you're left with having to drop the FK constraint and creating a new one once you alter the PK

 

[Crusty]

Or you could remove the rows that are linked to the primary key, store the data in a temporary table without the FK constraint. Then alter the PK, then re enter the rows that you removed before with the new PK.

 

[tfinch2]

You don't have the constraint set to "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE"?

 

[Markbnj]

OP, you understand what a constraint is, right? It's what is constraining you from doing the thing you're trying to do. That's why it's there.

This constraint says that the foreign key in the dependent table is a primary key in the master table, and that therefore you may not remove or alter the record in the master table if there is a record in the dependent table that depends on it. It's to keep you from trashing the database accidentally.

That doesn't mean you can't do what you want, but as the other replies suggest you have to do it explicitly, i.e. drop the constraint, make the change, fix both records, and reapply the constraint, or take one of the other approaches mentioned.

 

[acole1]

Yes, I do understand what a constraint is...

I had considered using the methods mentioned above, but I wasn't sure if there was a better way.

I'm about to work on it again, and I'll let you know what worked and didn't work.

Thanks!

Update:

Here is the code I am now running, and the error I get...

Code:
ALTER TABLE Commission
DROP PRIMARY KEY CASCADE;

UPDATE Commission
SET comm_class_id = '2801'
WHERE comm_class_id = '2101';

ALTER TABLE Commission
ADD PRIMARY KEY (comm_class_id);

ALTER TABLE Employee
ADD FOREIGN KEY (comm_class_id) REFERENCES Commission;

Error:
ORA-02298: cannot validate (AMC****.SYS_C00221629) - parent keys not found

 

[lozina]

That error means you have some record(s) in Employee who have a comm_class_id which does not exist in the parent table, Commission. So it refuses to let you set a constraint since the data is not valid to start with.

Chances are, since you changed the ID of a record in commission from 2101 to 2801 that there are records in Employee which still are using the 2101 ID, so you'll have to update those first then you can add the constaint.

Is all you did change the comm_class_id for one record? I find it kind of strange you even wanted to (or needed) to do that. But if that's all you needed to do it would have been easier to just create a new record in Commission for id 2801, copying everything for record 2101 doing a query like:

insert into Commission (comm_class_id, colA, colB, etc...) select 2801, colA, colB, etc... from Commision where comm_class_id = 2101
then:
update Employee set comm_class_id = 2801 where comm_class_id = 2101
then finally:
delete from Commission where comm_class_id = 2101

then you wouldnt have needed to mess with the constraints at all

 

[acole1]

Ah, OK! That error makes sense now! It's because the FK in Employee no longer matches up with a PK in Commission so it can't make the link.


This is the code I am using now, I get no errors, and the comm_class_id data is correct in both tables...

ALTER TABLE Commission
DROP PRIMARY KEY CASCADE;

UPDATE Commission
SET comm_class_id = '2801'
WHERE comm_class_id = '2101';

UPDATE Employee
SET comm_class_id = '2801'
WHERE comm_class_id = '2101';

ALTER TABLE Commission
ADD PRIMARY KEY (comm_class_id);

ALTER TABLE Employee
ADD FOREIGN KEY (comm_class_id)
REFERENCES Commission (comm_class_id);


Does that seem like it's the correct way of doing it?

Thanks for the replies!