I seem to have an issue where I can't modify a table due to another tables foreign key association:
trinity=# \d 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) |
"developers_pkey" PRIMARY KEY, btree (id)
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':
trinity=# SELECT id FROM developers;
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.
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?
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.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert