Re: Unable To Modify Table

On Thursday, January 12, 2012 8:42:59 am Carlos Mennens wrote:
> 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)
> [/CODE]
>

>
> 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".
> [/CODE]
>
> 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?

You are pushing in the wrong direction. You need to make the change in the table
'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com