How to replace a string in a column, which is a foriegn key in another Table

Question

I am trying to use an SQL replace query:

UPDATE mytable SET theId = Replace(theId, 'E', 'T')

The problem is theId is a key in another table mytable2 as theNumber

The error I get is:

ERROR: insert or update on table "mytable" violates foreign key constraint "mytable_theId_a0b4efa1_fk_mytable2_theNumber"
SQL state: 23503
Detail: Key (theId)=(763755.46T292326.83N) is not present in table "mytable2".

It is like I have to do a join replace or something somehow at the same time, no idea how to do this. Or maybe I have to alter the table to get rid of the relationship briefly, do the changes and add the relationship back in somehow? (no idea how to drop the keys etc..) (Looking in pgAdminIII I can't even see where I would get the name of the key to drop and re-add)

I am trying to change some values basically a string replace.

763755.46E292326.83N

To this:

763755.46T292326.83N

Answer 1

Alter table myTable2 DROP CONSTRAINT mytable_theId_a0b4efa1_fk_mytable2_theNumber 

UPDATE mytable SET theId = Replace(theId, 'E', 'T')

Alter table myTable2  ADD CONSTRAINT FK_myTable2_theID FOREIGN KEY(theNumber) REFERENCES myTable2(theId)