The Old Joel on Software Forum: Part 3 (of 5) - Oracle Question

Oracle Question - Update hassle

I have two tables.

Table 1
Col 1 (PK)
Col 2 (FK)
Col 3

Table 2
Col A (PK)

Table 1 have a One-to-One relationship with Table 2

Table 2 has a Zero-to-Many relationship with Table 1

I need to update a record in Table 1 - the only column I need to update in the record is Col 2.

I think because Col 2 (in Table 1) is a Foreign Key, I receive the following error message:

integrity constrain (Table 1_FK2) violated - parent key not found

My update statement is :

Update Table 1 set col 2 = 'FOO' where col 3 = 'BAR'

The parent table already has the value.

Thanks,

Enigma is just a puzzle
Monday, December 1, 2003

I love doing tech support. The issue is you have rows in the table 2 which rely on the row you are updating and so the integrity constraint will fail because they have nothing to relate to in table 1.

some values

table1:
a|1|data
b|2|data
c|1|data
d|1|data

table2:
1
2

if you update row 2 in table 1 (update table1 set col2 = '1' where col2 = '2') then you will get an oracle error because row two in table2 requires that row. you need to either update row 2 in table2 and and then run the update on table1 or find some other way of doing your data.

hope this helps (and/or makes sense)

ko
Monday, December 1, 2003

Ko is incorrect.

You could DELETE * FROM table1 and table 2 would not notice nor care.

You say that the parent table already has the value. Is col2 a varchar? If so, then make sure that the case is correct.

Still stuck? Try searching http://asktom.oracle.com/

Paul Sharples
Monday, December 1, 2003

One other thing: the PK column values in Table2 may contain trailing spaces (again, if the coltype is varchar2). These will need to be included in any values in table1.col2.

Paul Sharples
Monday, December 1, 2003

You could do some trigger evilness to solve this problem.

Do a BEFORE UPDATE trigger on table1, that checks if the field being updated is the FK (Col2). If so, you can update table 2 Col A from the trigger code via the :OLD and :NEW
variables that you can access before update.

If you update table2 accordingly first, your update of table1 will work with the constraint still enforced.

On the other hand, I dont know if that is what you want.

Patrik
Monday, December 1, 2003