Skip site navigation (1) Skip section navigation (2)

Unable To Modify Table

From: Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>
To: "PostgreSQL (SQL)" <pgsql-sql(at)postgresql(dot)org>
Subject: Unable To Modify Table
Date: 2012-01-12 16:42:59
Message-ID: CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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 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)
[/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?

Responses

pgsql-sql by date

Next:From: Steve CrawfordDate: 2012-01-12 16:53:47
Subject: Re: Unable To Modify Table
Previous:From: Gerardo HerzigDate: 2012-01-12 14:09:55
Subject: Re: amount of join's and sequential access to the tables involved

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group