Re: Unable To Modify Table - Mailing list pgsql-sql

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Carlos Mennens
Sent: Thursday, January 12, 2012 11:43 AM
To: PostgreSQL (SQL)
Subject: [SQL] Unable To Modify Table

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 nullname    | character(50)  | not
nulladdress| 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"
FOREIGNKEY (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
------------100000000110000000021000000003100000000410000000051000000006
(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?

---------------------------------------------------------------

There are two possible actions you can take with respect to an existing
Primary Key; you can UPDATE it or you can DELETE it.  When you define a
FOREIGN KEY you can specify what you want to happen if the corresponding
PRIMARY KEY is UPDATEd or DELETEd.  Read the documentation on FOREIGN KEY in
detail to understand why you are seeing that error and what modifications
you can make to the FOREIGN KEY on "orders" to obtain different behavior.

Keep in mind, also, that the TYPE of the PRIMARY KEY and FOREIGN KEY must
match.

Contrary to my earlier advice assigning a sequential ID (thus using a
numeric TYPE) is one of the exceptions where you can use a number even
though you cannot meaningfully perform arithmetic on the values.  The reason
you would use a numeric value instead of a character is that the value
itself is arbitrary and the space required to store a number is less than
the space required to store a string of the same length.

There are many points-of-view regarding whether to use "serial" PRIMARY KEYs
but regardless of whether you add one or not you should try and define a
UNIQUE constraint on the table by using meaningful values.  However, for
things like Orders this is generally not possible and so you would want to
generate a sequential identifier for every record.

David J.