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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2012-01-12 16:53:47 | Re: Unable To Modify Table |
Previous Message | Gerardo Herzig | 2012-01-12 14:09:55 | Re: amount of join's and sequential access to the tables involved |