From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Unable To Modify Table
Date: Thu, 12 Jan 2012 08:54:41 -0800
User-Agent: KMail/1.13.5 (Linux/2.6.32-37-generic; KDE/4.4.5; i686; ; )
Cc: Carlos Mennens <carlos.mennens@gmail.com>
References: 
 <CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com>
In-Reply-To: 
 <CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201201120854.42306.adrian.klaver@gmail.com>

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.klaver@gmail.com