Unable To Modify Table

CarlosinFL · #1 (**permalink**) 01-12-12, 11:41

I seem to have an issue where I can't modify a table due to another tables foreign key association:

Code:

trinity=# \d developers
         Table "public.developers"
    Column    |      Type      | Modifiers
--------------+----------------+-----------
 id      | character(10)  | not null
 name    | character(50)  | not null
 address | character(50)  |
 city    | character(50)  |
 state   | character(2)   |
 zip     | character(10)  |
 country | character(50)  |
 phone   | character(50)  |
 email   | character(255) | 
Indexes:
    "developers_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) REFERENCES developers(id)

Now I want to change the formatting of field data in 'id' in table 'developers':

Code:

trinity=# SELECT id FROM developers;
     id
------------
 1000000001
 1000000002
 1000000003
 1000000004
 1000000005
 1000000006
(109 rows)

Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table.

Code:

trinity=# UPDATE developers SET id = '1000' WHERE id = '1000000001';
ERROR:  update or delete on table "developers" violates foreign key constraint "fk_orders_developers" on table "orders"
DETAIL:  Key (id)=(1000000001) is still referenced from table "orders".

How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint?

loquin · #2 (**permalink**) 01-16-12, 18:40

The idea is to avoid 'orphan' records, by limiting the ability to change records where dependent records exist. IOW, you maintain the integrity of the data.

If you have a situation where you sometimes need to alter data in the way you mention, try altering/replacing the existing constraint to take advantage of pg's UPDATE CASCADE feature of the foreign key constraint.

Internet Services

Web Development

Digital Marketing

Consumer Tech